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

Question

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');

Gordon Linoff · Accepted Answer · 2019-06-07 19:06:20Z

2

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;

edited Jun 7, 2019 at 19:06

answered Jun 7, 2019 at 15:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sicisum Over a year ago

This program for these tables is an all over nightmare. We are merging our users with another set, so there is existing data there.

sicisum Over a year ago

The second solution works! Thank you so much Gordon!

UnhandledExcepSean Over a year ago

More for the OP's benefit: It is likely beneficial to left join to the SiteSecurity table and add a SiteSecurity.SiteID IS NULL to the WHERE to ensure you don't insert duplicates

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related