Copy table data from SQL Server 2000 to SQL Server 2008

1
votes

I have a SQL Server 2000 database with 7 tables. I want to copy the table data from this database to another database running on a SQL Server 2008 instance.

The tables will be exactly the same, I just need to copy the data from one to another.

Edit:

I get the following errors when trying to import from SQL Server 2008, I even check the box drop destination tables.:

Validating (Error)
Messages
Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "AllowedHtmlSystemID".
(SQL Server Import and Export Wizard)

Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
(SQL Server Import and Export Wizard)

Error 0xc004706b: Data Flow Task 1: "component "Destination 1 - DnnBBAllowedHtml" (79)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)

Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
(SQL Server Import and Export Wizard)

Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
(SQL Server Import and Export Wizard)

EDIT:

enter image description here

3
sqlsql-server-2008sql-server-2000ssms
What have you tried already? Do you want to move them programmatically, or just move them via SQL tools? – techie007
I get the following error: (see edit) – SOLDIER-OF-FORTUNE

3 Answers

6
votes

It looks like you are trying to insert data into an identity column. If you need the orginal identity values, first make sure they are not currently being used, then in the wizard, click the Edit Mapping button and check the Enable Identity Insert box. If you want to generate new ids, then in the destination in the mapping section, change the value to "ignore" for the identity field.

4
votes

As an alternative to the "Import data..." approach, you could also generate a sql script to insert the data into the other database.
Here's how to do it:
Right-click on the database and select Tasks->Generate scripts...

Select the specific tables that you'd like to copy (on the wizard page that lets you select the database objects)
When you get to the page that asks "Specify how scripts should be saved or published.":
-click the Advanced button
-For Types of data to script: Change "Schema only" to "Data only"
Finish clicking through the wizard to generate your .sql file.

Then you can use the created .sql file to insert the data into your other database. NOTE: Be sure to change the USE statement to specify your destination database.

3
votes

Right click on your sql2008 database in SSMS, then 'Tasks' - 'Import data...' and just follow the SSIS wizard.