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


Greetings to all, friends. I have the following problem:

I just assumed the administration of an old system which manages 4 databases (different names, identical structure) but the only common table in structure and data is that of Users.

The system, as they inform me, has a task that takes the table of one of the databases as the main one and replicates it in the other 3: S, this task is failing and the system is down ...: $

While I must look at what part of this horrible code is this task, I urge you to replicate the tables manually via SQL Server .

I tried this:

UPDATE [BDdestino].[dbo].[Usuarios]

[DBdestino].[dbo].[Usuarios].activo = [Usuarios].activo,
[DBdestino].[dbo].[Usuarios].apellido = [Usuarios].apellido,
[DBdestino].[dbo].[Usuarios].cargo = [Usuarios].cargo,
[DBdestino].[dbo].[Usuarios].correo = [Usuarios].correo,
[DBdestino].[dbo].[Usuarios].nombre = [Usuarios].nombre,
[DBdestino].[dbo].[Usuarios].password = [Usuarios].password,
[DBdestino].[dbo].[Usuarios].usuario_id = [Usuarios].usuario_id

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

But I get this error:


Msg 1013, Level 16, State 1, Line 2 Objects   "DBdestino.dbo.Users" and "DBorigen.dbo.Users" in the FROM clause   They have the same names exposed. Use correlation names for   distinguish them.

asked by Guillermojortizr 01.03.2017 в 19:23

2 answers


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;
answered by 01.03.2017 / 20:22

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

UPDATE [BDdestino].[dbo].[Usuarios]
[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

[dbo].[Usuarios].usuario_id = usu1.usuario_id
answered by 01.03.2017 в 19:34