asked on

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

mimran18

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

ASKER CERTIFIED SOLUTION

pivar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Kakhaber Siradze

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wobbled

ASKER

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

mimran18

Hi,
Supplier ID will be an identity column ?

wobbled

ASKER

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

wobbled

ASKER

Thanks - pointed me in the right direction