[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

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
0
wobbled
Asked:
wobbled
2 Solutions
 
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
 
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
 
AngelgeoCommented:

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

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now