How to Exclude records from an insert

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

General SQL Server Forums

New to SQL Server Programming

How to Exclude records from an insert

Author

Topic

gregoryagu
Yak Posting Veteran

80 Posts

Posted - 2008-07-17 : 17:34:01

This is the SQL I have written so far for an insert statement

INSERT INTO ObjectXRef
(objectid1,
objectid2,
type1,
type2)
SELECT U.userid AS objectid1,
D.pageguid as objectid2,
1 as type1,
64 as type2
FROM dbo.fn_Users_GetActive() U
CROSS JOIN dbo.DefaultChartPages D
ORDER BY userid

I would like to add a where clause to this so that if such a record already exists, it is not inserted. Something like

WHERE NOT EXISTS (SELECT * FROM ObjectXRef where objectid1 = T.objectid1, objectid2 = T.objectid2, type1 = 1, type2 = 64)

where T is the result set from the above cross join.

Any suggestions?

Thanks,

Greg

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 18:08:13

[code]INSERT ObjectXRef
(
objectid1,
objectid2,
type1,
type2
)
SELECT DISTINCT U.userid,
D.pageguid,
1,
64
FROM dbo.fn_Users_GetActive() as U
CROSS JOIN dbo.DefaultChartPages as D
LEFT JOIN ObjectXRef AS y ON y.objectid1 = U.userid
AND y.objectid2 = D.pageguid
and y.type1 = 1
and y.type2 = 64
WHERE y.objectid1 IS NULL
ORDER BY u.userid[/code]or[code]INSERT ObjectXRef
(
objectid1,
objectid2,
type1,
type2
)
SELECT DISTINCT U.userid,
D.pageguid,
1,
64
FROM dbo.fn_Users_GetActive() as U
CROSS JOIN dbo.DefaultChartPages as D
WHERE NOT EXISTS (SELECT * FROM ObjectXRef AS y WHERE y.objectid1 = U.userid AND y.objectid2 = D.pageguid and y.type1 = 1 and y.type2 = 64)
ORDER BY u.userid[/code]

E 12°55'05.25"
N 56°04'39.16"

gregoryagu
Yak Posting Veteran

80 Posts

Posted - 2008-07-17 : 18:34:48

Thanks very much.

Greg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources