sql help required

[Jami]

sql 2000
i have following 2 tables

CREATE TABLE [idtab] (
[uid] [int] NOT NULL ,
[uid2] [int] NOT NULL ,
[system] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_idtab] PRIMARY KEY CLUSTERED
(
[uid],
[uid2],
[system]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [sectab] (
[id1] [int] NULL ,
[id2] [int] NULL ,
[system] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

i m populating values from sectab to idtab
as idtab contains composite primary key i only wants to insert those
values which does not exist in idtab

some samlpe data is

insert into idtab values (1,2,'a')
insert into idtab values (1,3,'a')
insert into idtab values (1,3,'b')

go

insert into sectab values (1,2,'a')
insert into sectab values (1,4,'a')
insert into sectab values (1,3,'b')
insert into sectab values (1,5,'c')
insert into sectab values (2,3,'a')
insert into sectab values (3,3,'b')

what will be the query for inserting records

thanx

*** Sent via Developersdex http://www.developersdex.com ***

[Dan Guzman]

> i m populating values from sectab to idtab
> as idtab contains composite primary key i only wants to insert those
> values which does not exist in idtab

Thanks for the DDL and sample data. One method is WHERE NOT EXISTS:

INSERT INTO dbo.idtab

(
[uid]
,[uid2]
,[system]
)

SELECT
[id1]
,[id2]
,[system]
FROM dbo.sectab
WHERE NOT EXISTS(
SELECT *
FROM dbo.idtab
WHERE
sectab.id1 = idtab.uid
AND sectab.id2 = idtab.uid2
AND sectab.system = idtab.system
)

Another approach is a LEFT JOIN:

INSERT INTO dbo.idtab

(
[uid]
,[uid2]
,[system]
)

SELECT
sectab.[id1]
,sectab.[id2]
,sectab.[system]
FROM dbo.sectab
LEFT JOIN dbo.idtab ON
sectab.id1 = idtab.uid
AND sectab.id2 = idtab.uid2
AND sectab.system = idtab.system
WHERE
idtab.uid IS NULL

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/