Copy columns and its data from a table in one database to another table in a different server

0
votes

I have 2 tables, TABLE1 with columns col1, col2, col3, col4 and col5 in a database on server1. I have TABLE2 with the same columns and almost same data as TABLE1 excluding columns col2 and col5 in a database on server2. TABLE2 has the same data in col1, col3 and col4 as TABLE1. Now I know I can just create a new table and use the import/export feature of sql-server. But this would not be feasible if the tables had tons of data and more columns.

So my question is there any way to insert data in col2 and col5 to TABLE2 through any other means? If this is possible I would like to make sure the data that is being inserted is col2 and col5 in TABLE2 matches to that of TABLE1 meaning if I have col1 with data lets say 5 and col2 has data xyz, would like this data to be inserted at this row. (FYI col1 is the primary key on both tables).

Both servers are sql server 2008r2.

1
sqlsql-serverdatabasesql-server-2008tsql
Why don't you just add new columns col2 and col5 into table2 on server2 and insert all at the same time? Alternatively, if you've imported the data before adding the columns, then you can just join on your PK and update the newly added columns. – RoKa
Right, I can do that between 2 tables on the same database in the same server. How would I do it when moving those data to a different server? Would I have to do anything different? I am quite new to sql servers. – croxfade
You'll need to create a linked server. Details are in the answer below. – RoKa

1 Answers

1
votes

Provided the two SQL servers are on the same network, make a linked server on server2.

How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005

You may need to do an IDENTITY INSERT should you wish to keep the Id columns the same.

How to turn IDENTITY_INSERT on and off using SQL Server 2008?

You would be able to write an INSERT statement along the lines of:

INSERT INTO [server2].[database1].[table1] ( col1, col3, col5 )
SELECT col1, col3, col5 FROM [server1].[database1].[table1]

If you don't want to introduce duplicate keys, use a WHERE NOT EXISTS col1 IN ( ... ).

If you don't want to introduce duplicate rows, use a EXCEPT statement.

Hope this helps you.