Search code examples
sql-serverdatabase-designautonumber

how to export register from a table with autonumber iD?

I have a database which all the tables have as ID a autonumeric field. I think that is the best option, but if I need to export data from a database to another, how can I do that? Because I can insert a value in the autonumeric fields.

Is a good option the autonumeric or just gives more problems than benefits? If is better in general that I set the ID, which is a good method to decide the ID?

thanks.

Solution

  • Moving identity data from one database to another is not really a big deal. You can script the insert statements (including the IDENTITY column) and then for each destination table issue:

    SET IDENTITY_INSERT dbo.tablename ON;

-- ... inserts here

SET IDENTITY_INSERT dbo.tablename OFF;

    Some 3rd party tools will automate this for you, so you don't have to worry about it.