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
 Transact-SQL (2000)
 using not exists

Author  Topic 

cognos79
Posting Yak Master

241 Posts

Posted - 2006-11-07 : 16:11:36
I do have a table called "Orders" and its structure is like this

OrderId Name Address City

and there is second table called "Data" with structure

Id ShipName ShipAddress ShipCity

Now I want to insert records into "Data" table from "Orders" table which are not already present in "Data" table. If the record already exists in "Data" table it shouldnt be inserted.

How to write a query ????????

cognos79
Posting Yak Master

241 Posts

Posted - 2006-11-07 : 16:19:51
ronin...this is not what i need. you get my question wrong.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-07 : 16:21:54
This assumes that [ID] and OrderID is unique (in both tables).

insert [data]
([Id]
,ShipName
,ShipAddress
,ShipCity)
select OrderId
,[Name]
,Address
,City
from Orders o
where not exists
(
select 'cognos79'
from [data] d
where d.[id] = o.OrderID
)


Be One with the Optimizer
TG
Go to Top of Page

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-11-07 : 16:24:51
I must have but TG already gave you an answer
Go to Top of Page

cognos79
Posting Yak Master

241 Posts

Posted - 2006-11-07 : 16:25:39
TG...The Primary Key is composed of three fields not just OrderId field. How can i do it then
Go to Top of Page

cognos79
Posting Yak Master

241 Posts

Posted - 2006-11-07 : 16:32:02
I got the answer...thanks
Go to Top of Page
   

- Advertisement -