user7043412 user7043412 - 27 days ago 8
SQL Question

How to insert results from select (from db1) into table (in db2)

I have two databases with the same table. I have a connectionstring to connect to both databases.

How can I perform a

select from table A
in DB1 and insert the results in table
A
in DB2?

My connectionstrings:


  • For DB1:
    Server=12.34.56.789,12345;Database=shop;User Id=admin; Password=1234567890;

  • For DB2:
    Server=34.12.56.789,12345;Database=shop;User Id=admin; Password=1234567890;


Answer

try this one

/*do this from target server*/
EXEC sp_addlinkedserver @server='12.34.56.789,12345'
EXEC sp_addlinkedsrvlogin '12.34.56.789,12345', 'false', NULL, 'admin', '1234567890'

SELECT * INTO tableA 
FROM [12.34.56.789,12345].[shop].[dbo].[tableA]