Checking existence with insert... select OPENXML

Hi,
If I am inserting data into a table and I do not wish to insert if the row is already there I can write:
insert into t1 (c1,c2,c3)
select c1,c2,c3 from t2
where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and c3=t2.c3)
If my source query for the insert uses OPENXML is there any syntactical form that I can use to test for existence of the row in the destination table as I did above. I can always insert into a temp table of course and then use that as the source of the da
ta for the insert and use the first syntax form. But I was just curious whether this can be easily achieved without the use of a temp table.
Cheers
Ken
Yes, it is possible. You just need to add an alias to the OPENXML derived
table. For example:
INSERT INTO YourTable(CustomerID, ContactName)
SELECT CustomerID, ContactName
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20)) AS XM
WHERE NOT EXISTS
(
SELECT 1
FROM YourTable AS x
WHERE x.CustomerID = XM.CustomerID
)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ken Eng" <KenEng@.discussions.microsoft.com> wrote in message
news:C2A7166E-7506-4B51-986A-7013210B63CA@.microsoft.com...
Hi,
If I am inserting data into a table and I do not wish to insert if the row
is already there I can write:
insert into t1 (c1,c2,c3)
select c1,c2,c3 from t2
where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and
c3=t2.c3)
If my source query for the insert uses OPENXML is there any syntactical form
that I can use to test for existence of the row in the destination table as
I did above. I can always insert into a temp table of course and then use
that as the source of the data for the insert and use the first syntax form.
But I was just curious whether this can be easily achieved without the use
of a temp table.
Cheers
Ken
|||Many thanks
Ken
"Narayana Vyas Kondreddi" wrote:

> Yes, it is possible. You just need to add an alias to the OPENXML derived
> table. For example:
> INSERT INTO YourTable(CustomerID, ContactName)
> SELECT CustomerID, ContactName
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20)) AS XM
> WHERE NOT EXISTS
> (
> SELECT 1
> FROM YourTable AS x
> WHERE x.CustomerID = XM.CustomerID
> )
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ken Eng" <KenEng@.discussions.microsoft.com> wrote in message
> news:C2A7166E-7506-4B51-986A-7013210B63CA@.microsoft.com...
> Hi,
> If I am inserting data into a table and I do not wish to insert if the row
> is already there I can write:
>
> insert into t1 (c1,c2,c3)
> select c1,c2,c3 from t2
> where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and
> c3=t2.c3)
> If my source query for the insert uses OPENXML is there any syntactical form
> that I can use to test for existence of the row in the destination table as
> I did above. I can always insert into a temp table of course and then use
> that as the source of the data for the insert and use the first syntax form.
> But I was just curious whether this can be easily achieved without the use
> of a temp table.
> Cheers
> Ken
>
>