Szer Szer - 3 months ago 6
SQL Question

Find missing records in joined table SQL Server

Let's say I have 3 tables in SQL Server 2008,

Users
,
Site
and
UserSite
:

+-------+ +-------+ +-------------+
| User | | Site | | User Site |
+-------+ +-------+ +-------------+
| User1 | | Site1 | | User1 Site1 |
| User2 | | Site2 | | User1 Site2 |
| User3 | +-------+ | User1 Site3 |
+-------+ | User2 Site1 |
| User2 Site3 |
| User3 Site1 |
+-------------+


Desired result: for each
User
and
Site
combo there has to be a record in
UserSite
like this:

+-------------+
| User Site |
+-------------+
| User1 Site1 |
| User1 Site2 |
| User1 Site3 |
| User2 Site1 |
|*User2 Site2*| Inserted
| User2 Site3 |
| User3 Site1 |
|*User3 Site2*| Inserted
|*User3 Site3*| Inserted
+-------------+


I can only Insert new records and cannot rewrite table from scratch.

Is there a way to do this with SQL Server?

Answer

I might suggest a brute force approach. Truncate the table and insert all the values:

truncate table UserSite;

insert into UserSite(User, Site)
    select u.user, s.site
    from users u cross join sites s;

You can just add the new ones with a slightly more complex query:

insert into UserSite(User, Site)
    select u.user, s.site
    from users u cross join
         sites s
    where not exists (select 1
                      from usersite us
                      where us.user = u.user and us.site = s.site
                     );
Comments