adonetok (1/17/2014)
Company is doing server and application update.I need to copy about 100 tables from server A to server B.
Since the project is under test mode and I need to do the same copy job once week.
Can some expert tell me which way is the best way to do it?
Now, I use "Import data..." to do it but every time need to select a lot of tables.
I've done this hundreds of times with SSIS. You can also use BCP, but SSIS is probably easier. If both table's schemas are the same, you should be ok. You can create the Indexes after, which I do recommend, so your insertion process will be also faster.
Important! Set identity insert on on SSIS so you can preserve original IDs, in case you have one on each table.
You can compare both sets, after you finish, with following T-SQL query. Just adjust and change names accordingly:
--Compare both sets. It should return no rows.
SELECT n.ID AS [DeletedID], o.ID AS [InsertedID]
FROM dbo.Copy n
FULL OUTER JOIN dbo.Source o
ON (n.ID = o.ID)
WHERE o.ID IS NULL OR n.ID IS NULL
If source and target are the same, it will return no rows. Otherwise, above query will show you what was deleted and what was inserted.
Last but not least, be sure to run SSIS or copy from one table to other during off peak hours or a maintenance window. If not, you will generate some locks on the source table and you will take the chance or not getting both tables totally sync'ed.