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
237 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 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 the fundamental information of how to create a table.

691 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