How to copy the data of one table from one database into another database with an identical table?

If you want to replicate the data in a table using SQL, in reality a UPDATE judgment does not suit you, because you may have more or less records in both tables, and in those cases, the UPDATE It will not help you.

Rather, one option is to delete all the data and re-insert all the data from your source table:

delete from [BDdestino].[dbo].[Usuarios];

insert into [BDdestino].[dbo].[Usuarios] select * from [DBorigen].[dbo].[Usuarios];

If possible, instead of the DELETE , you could choose to use a TRUNCATE TABLE if you want.

In any case, if your tables contain a column identity ( usuario_id ?), you will need to run the following statement before doing the INSERT :

set identity_insert [BDdestino].[dbo].[Usuarios] on;

Another option that may be more efficient is to use a MERGE , which basically takes care of making the INSERT , UPDATE , DELETE necessary for your target table to become a replica of your source table. It can be more efficient if most of the records already agree between the 2 tables:

merge into [BDdestino].[dbo].[Usuarios] as dest
using [DBorigen].[dbo].[Usuarios] as src
   on src.usuario_id = dest.usuario_id
when matched then 
  update set dest.activo = src.activo,
             dest.apellido = src.apellido,
             dest.cargo = src.cargo,
             dest.correo = src.correo,
             dest.nombre = src.nombre,
             dest.password = src.password
when not matched then 
  insert (usuario_id, activo, apellido, cargo, correo, nombre, password)
  values (src.usuario_id, src.ctivo, src.apellido, src.cargo, src.correo, src.nombre, src.password)
when not matched by source then delete;

With the MERGE as well, if your usuario_id column is a identity column, you'll need to execute the following statement before the MERGE :

set identity_insert [BDdestino].[dbo].[Usuarios] on;

If the 2 bases are on the same server you can use an alias that avoids this inconvenience.

UPDATE [BDdestino].[dbo].[Usuarios]
SET
[DBdestino].[dbo].[Usuarios].activo = usu1.activo,
[DBdestino].[dbo].[Usuarios].apellido = usu1.apellido,
[DBdestino].[dbo].[Usuarios].cargo = usu1.cargo,
[DBdestino].[dbo].[Usuarios].correo = usu1.correo,
[DBdestino].[dbo].[Usuarios].nombre = usu1.nombre,
[DBdestino].[dbo].[Usuarios].password = usu1.password,
[DBdestino].[dbo].[Usuarios].usuario_id = usu1.usuario_id

FROM [DBorigen].[dbo].[Usuarios] usu1

where 
[dbo].[Usuarios].usuario_id = usu1.usuario_id