September 10, 2002 at 9:58 am
when I run an "insert into yyy select x, y z from ddd" query and one of the records to be inserted violates the primary key, the process is terminated and none of the records are appended. Is there a way to write the query so that the records that do not viloate the prmary key are appended?
thanks
September 10, 2002 at 10:11 am
might try something like this:
Create table yyy(x char(1) primary key, y char(1), z char(1))
create table ddd(x char(1), y char(1), z char(1))
insert into yyy values('A','B','C')
insert into ddd values('A','B','C')
insert into ddd values('X','Y','Z')
insert into yyy select x, y, z from ddd a
where x not in (select distinct x from yyy )
drop table yyy,ddd
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 10, 2002 at 10:13 am
thanks gregory, but my table has more than one column in its primary key
September 10, 2002 at 10:24 am
Possibly then you could try something like this:
Create table yyy(x char(1), y char(1), z char(1)
primary key (x,y))
create table ddd(x char(1), y char(1), z char(1))
insert into yyy values('A','B','C')
insert into ddd values('A','B','C')
insert into ddd values('X','Y','Z')
insert into ddd values('X','D','z')
insert into ddd values('A','B','D')
insert into yyy select x, y, z from ddd a
where x+y not in (select distinct x+y from yyy )
drop table yyy,ddd
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply