Archive

There is no easy way that I am aware of (in MS SQL 2000). You basically have to create a table with an additional identity field and import the data. Here is how you do it:

-From *Enterprise Manager, backup the original table by renaming it.
-From Query Analyzer, create new table…
-Right click the old table “Script Object to new window as ” -> “create”.
-Edit the ALL table names in the query to the original. (this makes a copy of the table structure)
-Execute.
-In Enterprise manager create new ID (identity column) and set the identity property to “yes, not for replication” in the newly created table (you may also want to set this column as the primary key).
-Import the data…
-Run this query in Query Analyzer, replacing the appropriate table names and column names. NOTE: the column names must be in the same order for both tables!
INSERT INTO [new_table] (list_all_the_column_names_to_import) SELECT list_all_the_column_names_to_import FROM [old_table]

* If you know your SQL query lingo well enough you can do everything from Query Analyzer.

:)

Category: SQL, microsoft author: admin comments: No Comments