copy records 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

SQL Server 2000 Forums

SQL Server Development (2000)

copy records from one table to another

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-10-26 : 08:01:33

Manbir writes "I have no idea how to start? Help??
I have two tables

Example
Table - AA with fields - a, b, c, d, e
Table - AB with similar fields.

I need to insert those records from AA to AB which are not already there in AB. i.e, I do not want duplicate records in AB as a result of this insert.

Duplicate records are identified by a combination of field a,b and c.

Along with this insert I need to create a new table and insert those records in this table that were inserted in AB.

Any Idea?"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-26 : 08:23:20

[code]
insert into AB (a, b, c d, e)
select a, b, c, d, e
from AA
where not exists (select * from AB where AB.a = AA.a and AB.b = AA.b and AB.c = AA.c)
[/code]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-10-26 : 08:29:18

quote:

Along with this insert I need to create a new table and insert those records in this table that were inserted in AB.


select a, b, c, d, e Into SomeTable
from   AA 
where  not exists (select * from AB where AB.a = AA.a and AB.b = AA.b and AB.c = AA.c)

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-26 : 08:52:00

quote:
Along with this insert I need to create a new table and insert those records in this table that were inserted in AB.

I would recommend to create the table first and then insert records using INSERT...SELECT, for performance reason.

Harsh Athalye
India.
"Nothing is Impossible"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-26 : 09:00:03

"Along with this insert I need to create a new table and insert those records in this table that were inserted in AB."
Oh... missed out this. In that case, you should insert into that table first before insert into AB


select a, b, c, d, e Into SomeTable
from   AA 
where  not exists (select * from AB where AB.a = AA.a and AB.b = AA.b and AB.c = AA.c)

insert into AB (a, b, c d, e)
select a, b, c, d, e
from   AA 
where  not exists (select * from AB where AB.a = AA.a and AB.b = AA.b and AB.c = AA.c)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-26 : 09:39:09

so why not

select a, b, c, d, e Into SomeTable
from   AA 
where  not exists (select * from AB where AB.a = AA.a and AB.b = AA.b and AB.c = AA.c)

insert into AB (a, b, c d, e)
select a, b, c, d, e
from   SomeTable

yea, yea, where clause is ALWAYS good to have. just presenting a different solution.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources