Append data from one SQL server to another

[load3d]

I am new to SQL. I'm not sure where to start if anyone knows a of a good tutorial feel free to send it over. It won't take me logn to get the jist of it.

I have two SQL servers that I am working with right now. One of the databases has a table of all employees that gets added when we hire someone new. I want to append that data into a database on another SQL server daily without duplication. What is the best way to do this?

 

[RiverGuy]

The easiest way IMO would be to add a linked server to your source server on the destination server.

See here

http://msdn.microsoft.com/en-us/library/ms188279(SQL.90).aspx

You could then write a simple SQL script to append data from the source to the destination, create a job and schedule it.

The actual SQL script will vary based on the layout of your table. For example, if the data set is small enough, you might do a truncate/insert all each day. Or if you source has a "LastModified" column, you could select all records modified in the past day and do an update/insert script.

 

[load3d]

I do not have a last modified column in that table. I can go with appending all data because the table is only 858 rows and since it is employee data that really isn't going to grow a whole lot.

I did create a linked server on the SQL server that holds the employee data but, you are saying to create the linked server on the SQL server that I am appending the data too, the destination server, correct?

Thanks for the link and the prompt reply.

 

[tlbroadbent]

Your tables should have a PRIMARY KEY or unique index. If so, then the code to only insert new records is quite simple. While there are multiple ways to accomplish this, I prefer LEFT OUTER joins.

This code assumes you are running the query on the source server and col1 is the PRIMARY KEY. If the PRIMARY KEY is compound (multiple columns) you'll have to use all columns in the ON clause of the JOIN.

INSERT destinationserver.destinationdb.dbo.Employees
(col1, col2, col3, col4)
SELECT col1, col2, col3, col4
FROM sourcedb.dbo.Employees s
LEFT JOIN destinationserver.destinationdb.dbo.Employees d
  ON s.col1 = d.col1
WHERE d.col1 IS NULL

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)