Copy a row of data from one table to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Copy a row of data from one table to another

Author

Topic

Ernie57
Starting Member

27 Posts

Posted - 2006-06-08 : 08:28:52

Hello,

I want to move or copy a row of data from one table to another, but not sure how to go about it! I have SQL 2000, and using Enterprise Mangager with SQL query to run the script. This will be used in a procedure once I get the Query to work.

Thank you,

Ernie

Kristen
Test

22859 Posts

Posted - 2006-06-08 : 08:52:46

INSERT INTO TargetTable
SELECT *
FROM SourceTable
WHERE ...

but you should do:

INSERT INTO TargetTable(... Column List ...)
SELECT ... Column List ...
FROM SourceTable
WHERE ...

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 08:53:03

[code]insert into target_table(cola, colb, colc, . . .)
select col1, col2, col3, . . .
from source_table
where <some condition>
[/code]

Ernie57
Starting Member

27 Posts

Posted - 2006-06-08 : 17:57:42

quote:
Originally posted by Kristen

INSERT INTO TargetTable
SELECT *
FROM SourceTable
WHERE ...

but you should do:

INSERT INTO TargetTable(... Column List ...)
SELECT ... Column List ...
FROM SourceTable
WHERE ...

Kristen

Thank you,

Ernie

I did get this to work with your advice.

Ernie57
Starting Member

27 Posts

Posted - 2006-06-09 : 22:25:05

SET IDENTITY_INSERT Yahoo ON

Insert into Yahoo (Category1, Category2, Category3, Category4, Category5, Category6, Category7, Category8, Title, Description, Quanity, Total, StartPrice, Duration, Image1, Picture1, Image2, Picture2, Image3, Picture3, enablePP, shipprice, isbn, upc, Barcode, TimeStamp1, UnitID, UserName, Location, Status, SetDate)

Select Category1, Category2, Category3, Category4, Category5, Category6, Category7, Category8, Title, Description, Quanity, Total, StartPrice, Duration, Image1, Picture1, Image2, Picture2, Image3, Picture3, enablePP, shipprice, isbn, upc, Barcode, TimeStamp1, UnitID, UserName, Location, Status, SetDate

From Sales

Where Status = 'Auction'
AND
Where Sales.dbo.Barcode = Yahoo.dbo.Barcode

SET IDENTITY_INSERT Yahoo off
GO

Everything works except the extra Where statement. How do I check if the Barcode resides in the table before doing the insert? I want to meet two conditions, the Auction, and the exstance of the barcode.

This same record was already copied from Yahoo table to Sales, but under a condition that the status would change on the record, it would be copied back to Yahoo table.

Thank You,

Ernie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-10 : 06:30:45

You need UPSERT

General Approach

Update T
set col1=S.col1, col2=S.col2,...
from TargetTable T inner join SourceTable S
on T.PrimaryId=S.PrimaryId

Insert into TargetTable(columns)
Select columns from SourceTable S where not exists
(select * from TargetTable where PrimaryId=S.PrimaryId)

The table design is not normalised
Read this
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

Ernie57
Starting Member

27 Posts

Posted - 2006-06-10 : 18:40:27

quote:
Originally posted by madhivanan

You need UPSERT

General Approach

Update T
set col1=S.col1, col2=S.col2,...
from TargetTable T inner join SourceTable S
on T.PrimaryId=S.PrimaryId

Insert into TargetTable(columns)
Select columns from SourceTable S where not exists
(select * from TargetTable where PrimaryId=S.PrimaryId)

The table design is not normalised
Read this
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

I have not had a chance to run the script, but I will try later!

Thank You,

Ernie

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-06-12 : 15:32:37

quote:

SET IDENTITY_INSERT Yahoo ON

Insert into Yahoo (Category1, Category2, Category3, Category4, Category5, Category6, Category7, Category8, Title, Description, Quanity, Total, StartPrice, Duration, Image1, Picture1, Image2, Picture2, Image3, Picture3, enablePP, shipprice, isbn, upc, Barcode, TimeStamp1, UnitID, UserName, Location, Status, SetDate)

Select Category1, Category2, Category3, Category4, Category5, Category6, Category7, Category8, Title, Description, Quanity, Total, StartPrice, Duration, Image1, Picture1, Image2, Picture2, Image3, Picture3, enablePP, shipprice, isbn, upc, Barcode, TimeStamp1, UnitID, UserName, Location, Status, SetDate

From Sales

Where Status = 'Auction'
AND
Where Sales.dbo.Barcode = Yahoo.dbo.Barcode

SET IDENTITY_INSERT Yahoo off
GO

Everything works except the extra Where statement.

Ernie,

Just delete the WHERE keyword immediately after AND, so it reads

Where Status = 'Auction'
AND
Sales.dbo.Barcode = Yahoo.dbo.Barcode

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources