Unique Insert Statement - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Unique Insert Statement

Author

Topic

SimpleMan
Starting Member

1 Post

Posted - 2011-04-06 : 11:57:08

I've got 3 tables associated with this query.

tblUserRights (UserID, LinkID) with PK's on both
tblUser (UserID, UserName) with PK on UserID
tblMenuLinks (LinkID, LinkName) with PK on LinkID

I need to add UserId and LinkID into tblUserRights if it doesn't already exists. I created a query that shows the combination of UserID and LinkID but when I try to insert them into tblUserRights I get an error because it would create duplicates.

This query would be run to ensure that tblUserRights is update to date and not missing a combination.

Here is what I have so far and will work if there are no duplicates:

	INSERT INTO tblUserRights
						  (UserID, LinkID)
	SELECT     tblUser.UserID, tblMenuLinks.LinkID
	FROM         tblUser CROSS JOIN
						  tblMenuLinks

Any suggestions on how to only insert records that don't exist?

Thanks.

Nick

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-06 : 12:34:06

[code]
Insert Into tblUserRights
Select Distinct A.UserId, A.LinkId
From
(
SELECT tblUser.UserID, tblMenuLinks.LinkID
FROM tblUser
CROSS JOIN tblMenuLinks
) A
Left Join tblUserRights B
On A.UserId = B.UserId
and A.LinkId = B.LinkId
Where B.UserId is null
[/code]

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources