Take a selection from a table and load it back into the same table with a different id

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
LVL 17
wobbledAsked:
Who is Participating?
 
pivarCommented:
Hi,

How about? ... are other fields you might want to copy

INSERT yourtable (SupplierId, PriceReturn, ...)
SELECT @newSupplierId, PriceReturn, ...
FROM yourtable
WHERE SupplierId = 1

/peter
0
 
mimran18Commented:
Hi
here we go.
 
Drop table test
Go
Create table test
(ID int Identity(1,1),
[SName] nvarchar(50)
)
Go
Insert  into test values ('John')
Insert  into test values ('Robert')
Insert  into test values ('William')
GO
insert into test Select [Sname]+'-2' from [test] where [Sname]='Robert'
GO
Select * from test

Open in new window

0
 
Kakhaber SiradzeCommented:

INSERT INTO mytble (SupplierId, row1,row2)
SELECT 2, row1,row2
FROM mytble 
WHERE SupplierId = 1

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
wobbledAuthor Commented:
Hi Pivar

I think your approach may be work - let me give it a go and see (there are only a few other columns - just for ease of asking the question I missed them out)

Mimran18 - not sure at all on the approach you have suggested, it doesn't really deal what I was asking in the question.  I need to select from the main table a selection of values based on id, with this retrieved data set I need to associate a new SupplierId and then reinsert this back into the main table.

Thanks
0
 
mimran18Commented:
Hi,
   Supplier ID will be an identity column ?
0
 
wobbledAuthor Commented:
HI Mimran18

"Supplier ID will be an identity column ?"

No - the supplierId links to a primary ID in the Suppliers table.  There are no identity columns in use on this table - it just holds Ids from other tables (supplier etc) and a value column
0
 
wobbledAuthor Commented:
Thanks - pointed me in the right direction
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.