?
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
Medium Priority
?
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1200 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 800 total points
ID: 36571982

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

Open in new window

0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

777 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