CODEWITHSUNDEEP
sqlsql-serversql-server-2008
sicisum
sicisum

Reputation: 31

SQL insert multiple rows with one static value, one variable ones

We have a database that has the following setup:

SiteSecurityID | SiteID | LoginName
---------------+--------+-----------
Auto Gen #       Number   Username

Now, we have 35 SiteID's and every user needs access to each one. I cannot get any query I've tried to do this successfully.

I can do it one by one using this method, but there has to be an easier way...

INSERT INTO SiteSecurity (SiteID, LoginName) 
VALUES ('1', 'user1'), ('2', 'user1');

Upvotes: 1

Views: 1678

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can generate all the rows using a cross join, assuming you have tables for sites and users:

INSERT INTO SiteSecurity (SiteID, LoginName)
    SELECT s.SiteId, u.UserName
    FROM Sites s CROSS JOIN
         users u;

If you don't you can still generate them on the fly. Table constructors vary by database, but this gives the approach:

INSERT INTO SiteSecurity (SiteID, LoginName)
    SELECT s.SiteId, u.UserName
    FROM (VALUES (1), (2), (3), . . . ) s(SiteID) CROSS JOIN
         (VALUES ('user1'),  ('user2'), . . . ) u(UserName);

EDIT:

If you have existing data, you can filter out duplicates before inserting:

INSERT INTO SiteSecurity (SiteID, LoginName)
    SELECT s.SiteId, u.UserName
    FROM (VALUES (1), (2), (3), . . . ) s(SiteID) CROSS JOIN
         (VALUES ('user1'),  ('user2'), . . . ) u(UserName) LEFT JOIN
         SiteSecurity ss
         ON ss.SiteId = s.SiteId AND ss.LoginName = u.UserName
    WHERE ss.SiteId IS NULL;

Upvotes: 2

Related Questions