0

I'm currently changing the Id field of table to be an IDENTITY field. This is simple: Create a temp-table, copy all the data to the temp-table, adjust all the references from and to the table to point from and to the new temp-table, drop the old table, rename the temp-table to the original name.

Now I've got the problem that the copy step is taking too long. Actually the table doesn't have too many entries (~7.5 million rows), but it still takes multiple hours to do this.

I'm currently moving the data with a query like this:

SET IDENTITY_INSERT MyTable_Temp ON

INSERT INTO MyTable_Temp ([Fields]) SELECT [Fields] FROM MyTable

SET IDENTITY_INSERT MyTable_Temp OFF

I've had a look at bcp in combination with cmdshell and a following BULK INSERT, but I don't like the solution of first writing the data to a temp-file and afterwards dumping it back into the new table.

Is there a more efficient way to copy or move the data from the old to the new table? And can this be done in "pure" T-SQL?

Keep in mind, the data is correct (no external sources involved) and no changes are being made to the data during transfer.

kajk
  • 126
  • 12
  • Second answer of this question will help you: http://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column – bruno Dec 10 '14 at 15:58
  • In that case I can't see how to alter the table to change its column to be an identity field. For me every entry in the Id field of my table is NOT NULL and UNIQUE, but something like "ALTER TABLE MyTable ALTER COLUMN MyTableId INT IDENEITY NOT NULL" doesn't work with the error "Incorrect syntax near the keyword 'IDENTITY'.". The answer doesn't specify how the alter statement should look like and I can't find an answer to this question. Am I missing something? – kajk Dec 10 '14 at 16:19
  • 1
    From the answer: Here's the trick: you can use SQL Server's ALTER TABLE...SWITCH statement to change the schema of a table without changing the data, meaning you can replace a table with an IDENTITY with an identical table schema, but without an IDENTITY column. The same trick works to add IDENTITY to an existing column. – bruno Dec 10 '14 at 16:28
  • 1
    In that case we were looking at two different answers (mine are sorted by "active" by default and not by votes). I'll have another look in that case. Sorry and Thanks for pointing out to what I couldn't see. Here the link directly to the answer mentioned: http://stackoverflow.com/a/1730868/1417088 – kajk Dec 10 '14 at 16:33
  • @bruno does that method keep the foreign keys for that column working? – Adrian Nasui Dec 10 '14 at 16:37
  • It's one of the caveats: You need to drop foreign keys before you do the switch and restore them after. – bruno Dec 10 '14 at 16:39

1 Answers1

1

Your approach seems fair, but the transaction generated by the insert command is too large and that is why it takes so long.

My approach when dealing with this in the past, was to use a cursor and a batching mechanism.

Perform the operation for only 100000 rows at a time, and you will see major improvements.

After the copy is made you can rebuild your references and eventually remove the old table... and so on. Be careful to reseed your new table accordingly after the data is copied.

Adrian Nasui
  • 1,054
  • 9
  • 10