SQL Server Performance Forum – Threads Archive

Exporting rows from datatbase to database

We have just setup a new SQL 2005 box which we are eventually transfering all our SQL 2000 databases across to. Some of the issues I’ve had so far is copying individual rows from one database to another. SQL Management Studio will not insert the records with new unique IDs. I could do this easily in Enterprise Manager. An example would be copying a record with an ID of 26 into another database. The script should insert the record but assign a new unique ID but it doesn’t <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> it just throws an error and fails. If the ID is higher than what it’s inserting into, it assigns the higher ID which is wrong as I want the ID after the last record. Example: IDs 250 251 252 253 254 691 ??? Should be 255, not 691. I’ve tried all the settings in SQL Management Studio to no avail. Any help would be great as it’s really begining to fustrate me.

Hi,
Why you are transferring row by row from 2000 to 2005.
If you want to transfer 2000 DB to 2005,
you can take backup of 2000 DB and restore it on 2005 server

Ah! I don’t think I’ve explained properly. I’ve already transfered the database via restoring a backup onto 2005, but some of our databases are still on 2000 and as part of the sites, some clients need copying onto other databases hence my problem above. I’m simply copying a few rows literly!

read in BOL about using: DBCC CHECKIDENT ( ‘table_name’, RESEED, new_reseed_value )

True, you can check for any mismatch of pages in thsi case to correct with the above statement. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

you can also turn off the identity column (only one table at a time) with IDENTITY_INSERT see:http://msdn2.microsoft.com/en-us/library/ms188059.aspx SqlSpec – a fast, cheap, and comprehensive data dictionary generator
for SQL Server 2000 and 2005 and Analysis Server 2005 – www.elsasoft.org

Compare the table schema on both SQL 2000 & 2005 tables in this case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

]]>