Solved

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

Posted on 2011-09-20
7
214 Views
Last Modified: 2012-05-12
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
Comment
Question by:wobbled
7 Comments
 
LVL 9

Expert Comment

by:mimran18
ID: 36571961
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
 
LVL 22

Accepted Solution

by:
pivar earned 300 total points
ID: 36571962
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
 
LVL 5

Assisted Solution

by:Angelgeo
Angelgeo earned 200 total points
ID: 36571982

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

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 17

Author Comment

by:wobbled
ID: 36571983
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
 
LVL 9

Expert Comment

by:mimran18
ID: 36572009
Hi,
   Supplier ID will be an identity column ?
0
 
LVL 17

Author Comment

by:wobbled
ID: 36572109
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
 
LVL 17

Author Closing Comment

by:wobbled
ID: 36585450
Thanks - pointed me in the right direction
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question