Take a selection from a table and load it back into the same table with a different id
Posted on 2011-09-20
I have a table that holds a lot of data. In short there are two key columns, SupplierId and PriceReturn.
I wish to create a test data set from some of the data that is already in there. To do this I wish to perform a simple selection such as Select * from mytble where SupplierId = 1 (should return approx 500,000 rows). I then wish to take the selected data set and load it into the same table, but with a new id.
I need the test data to exist in the same table as the original data, just with a new id. The orginal data will still exist in the table.
My problem is how best to do this. I did think about exporting the selection out to a temporary table and then appending it to the orignal table with the new id. Or is there a way I can do this without having to write to a temporay table?
I need to script this in SQL so am looking for the simplest way to write this - any help is much appreciated