We've upgraded a copy of our production databases (SQL Server 2000) to SQL Server 2008, and made a bunch of fixes & changes to stored procedures. Now I want to copy the current data out of production (SQL Server 2000) and push it into the new SQL Server 2008, and point the production applications to the new one. All the table schemas are unchanged.
I know that scripting all the tables as inserts to a file is one possibility. Is this the best method? Or are there better approaches? Are foreign keys going to be a problem?
Here are couple things you can do and all of these have advantages and disadvantages:
Manual scripting and linked servers:
Setup linked server connections and use INSERT INTO
INSERT INTO server2008.databaseName.schemaName.TableName (Col1, Col2, ..., Coln)
SELECT Col1, Col2, ..., Coln
FROM server2000.databaseName.schemaName.TableName
Mind the execution order of these. Make sure to first migrate tables that have no references to avoid issues with foreign keys
You can even temporary disable foreign keys and identity inserts on some tables
ALTER TABLE MyTable NOCHECK CONSTRAINT FK_Constraint
SET IDENTITY_INSERT tableName ON
-- INSERT STATEMENT HERE
SET IDENTITY_INSERT tableName OFF
ALTER TABLE MyTable CHECK CONSTRAINT FK_Constraint
Export Data Wizard
Already suggested by Tomasito – just make sure to execute scripts in correct order. First scripts for tables with no references , than tables that only reference data in previously inserted tables and so on….
Third party tools
There are many of these that will do the job for you. If this is a one-time task you can get the job done using trial version. Couple I know are ApexSQL Data Diff, SQL Data Compare, AdeptSQL Diff.