Inserting records that don't already exist

There is a good article at
http://support.microsoft.com/defaul...kb;en-us;315968 that shows ho
w
to use OPENXML to update records that exist and add new records that don't t
o
a table.
The question is how do you modify the SQL code to work with a table with a
three column primary key? The IN statement only works when selecting a
single column.
Any help would be much appreciated.
Thanks,
OldmanHi
You can try this way
Insert Into Employee
SELECT EmployeeId, FirstName, LastName
FROM OPENXML (@.hdoc, '/NewDataSet/Employee',1)
WITH (EmployeeId Integer, FirstName varchar(100), LastName varchar(100))
XMLEmployee
INNER JOIN Employee
Where Employee.EmployeeId <> XMLEmployee.EmployeeID AND
Employee.FirstName <> XMLEmployee.FirstName AND
Employee.LastName <> XMLEmployee.LastName
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Oldman" wrote:

> There is a good article at
> http://support.microsoft.com/defaul...kb;en-us;315968 that shows
how
> to use OPENXML to update records that exist and add new records that don't
to
> a table.
> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
> Any help would be much appreciated.
> Thanks,
> Oldman|||> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
You can use NOT EXISTS instead of NOT IN:
WHERE NOT EXISTS
(SELECT *
FROM Employee e
WHERE e.Col1 = XMLEmployee.Col1 AND
e.Col2 = XMLEmployee.Col2 AND
e.Col3 = XMLEmployee.Col3)
Hope this helps.
Dan Guzman
SQL Server MVP
"Oldman" <Oldman@.discussions.microsoft.com> wrote in message
news:E0FC51E3-8305-4641-AA72-D470984B9510@.microsoft.com...
> There is a good article at
> http://support.microsoft.com/defaul...kb;en-us;315968 that shows
> how
> to use OPENXML to update records that exist and add new records that don't
> to
> a table.
> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
> Any help would be much appreciated.
> Thanks,
> Oldman|||Let us assume that the pk is (employeeid, firstname, lastname), then you can
do:
insert into employee
select
employeeid,
firstname,
lastname
from
openxml (@.hdoc, '/newdataset/employee',1)
with (employeeid integer, firstname varchar(100), lastname varchar(100))
as x
left join
employee as e
on
e.employeeid = x.employeeid and
e.firstname = x.firstname and
e.lastname = x.lastname
where
e.employeeid is null
and e.firstname is null
and e.lastname is null
go
AMB
"Oldman" wrote:

> There is a good article at
> http://support.microsoft.com/defaul...kb;en-us;315968 that shows
how
> to use OPENXML to update records that exist and add new records that don't
to
> a table.
> The question is how do you modify the SQL code to work with a table with a
> three column primary key? The IN statement only works when selecting a
> single column.
> Any help would be much appreciated.
> Thanks,
> Oldman|||Thanks guys for your answers. Dan that worked!
Now that you told me I'm smacking my head because I knew of the EXISTS
keyword.
Thanks again.
"Dan Guzman" wrote:

> You can use NOT EXISTS instead of NOT IN:
> WHERE NOT EXISTS
> (SELECT *
> FROM Employee e
> WHERE e.Col1 = XMLEmployee.Col1 AND
> e.Col2 = XMLEmployee.Col2 AND
> e.Col3 = XMLEmployee.Col3)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oldman" <Oldman@.discussions.microsoft.com> wrote in message
> news:E0FC51E3-8305-4641-AA72-D470984B9510@.microsoft.com...
>
>