SQL Server: How to copy a row with an incremented primary key

So, today I had to copy a row in our database for testing purposes (needed the exact same data but with one small fix and compare it to the original). Due to fantastic database design the row so many rows that I had no interest in just writing an insert statement and copying the values by hand. I googled the internet and found several solutions that may work. The following was my favorite:

> select * from person

+ ------- + --------- + -------- +

| id      | name      | age      |

+ ------- + --------- + -------- +

| 1       | Magnus    | 29       |

| NULL    | NULL      | NULL     |

+ ------- + --------- + -------- +

I want to copy the row with id 1. This is a very short row with only three columns, so it would be easy to do by hand. Consider it a very small example.
Now, to copy this to another table do the following:

SELECT * INTO tmpperson FROM person

Then drop the id column from the temporary table:

ALTER TABLE tmpperson DROP COLUMN id

and insert it back:

INSERT INTO person SELECT * FROM tmpperson

Leave a Reply