help to query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

help to query

Author

Topic

ibin
Starting Member

26 Posts

Posted - 2009-10-29 : 07:48:12

i have table1 and table 2

create table temptable1
( Code nvarchar(max),
testno int)
create table temptable2
( Code nvarchar(max),
testno int,
date datetime)

code and testno form unique key.

now i want to insert all the records into temptable2 from temptable1 which does not exists in temptable2.

Any help to query plz..

for ex:
temptable1
t1 1
t2 2
t1 3
t1 4

temptable2
t1 1
t2 2
t1 3

final result after inserting should be
temptable2
t1 1
t2 2
t1 3
t1 4

Eli Kohen
Starting Member

6 Posts

Posted - 2009-10-29 : 08:33:40

Hi,

INSERT INTO temptable2 (Code, testno, [date]) SELECT *, NULL FROM temptable1

Devart team. Database managment and data access solutions.
www.devart.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-29 : 08:35:54

insert into temptable2(columns)
select columns from temptable1 as t1
where not exists(select * from temptable2 as t2 where t1.col1=t2.col1 and t1.col2=t2.col2)

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-29 : 08:37:03

insert into temptable2(cl,c2)
select cl,c2 from temptable1 except (select cl,c2 from temptable2)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Eli Kohen
Starting Member

6 Posts

Posted - 2009-10-29 : 09:36:37

Sorry, I have missed "which does not exists in temptable2" :)

Devart team. Database managment and data access solutions.
www.devart.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources