July 3, 2002 at 8:44 am
I want to duplicate some rows in a table and change the primary id field on the new rows. In affect I want to do something like:
SELECT * FROM tblA WHERE ID='abc'
UPDATE (copy of selected rows) SET ID='xyz'
INSERT INTO tblA (the updated copied rows)
I know I can do it by specifying all the field, move them to temp variables, and do an insert for each row. The question is, is there a shortcut way of doing this?
July 3, 2002 at 9:38 am
This should work for you:
SELECT * INTO #TMP1 FROM TBLA WHERE ID = 'abc'
UPDATE #TMP1 SET ID = 'xyz'
INSERT INTO TBLA SELECT * FROM #TMP1
July 5, 2002 at 9:20 am
Or - do it one statement:
INSERT INTO tblA
SELECT 'xyz' AS ID,...[all other column names]
FROM tblA
WHERE ID = 'abc'
To clarify what I'm saying in the SELECT statement, in the SELECT clause, don't use '*'; instead, list every column, but where you would normally list the ID column, replace it with the string constant 'xyz'.
Incidentally, if every row that you are inserting needs to have a different ID value, which would be the case if this is your primary key, then instead of using a string constant in the SELECT list, I would use a simple formula of some sort that would generate new IDs on the fly. The formula would be the same thing that you had in mind for use in an UPDATE statement.
Matthew Burr
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply