CODEWITHSUNDEEP
sqlsql-server
Eduard Pashchuk
Eduard Pashchuk

Reputation: 77

Inserting multiple rows into some table using values from select statement

It is possible to insert multiple rows in one table using values from select statement?
an example:

INSERT INTO SomeTable (UserID, ModuleID, ModuleRights) VALUES 
      (u.UserId, 1, 15),
      (u.UserId, 2, 1),
      (u.UserId, 4, 3),
      (u.UserId, 8, 7)
SELECT * FROM Users u

Upvotes: 2

Views: 1246

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269447

Yes, but you need to be careful how you do it. In this case, it appears you want a cross join:

INSERT INTO SomeTable (UserID, ModuleID, ModuleRights)
    SELECT u.UserId, v.ModuleID, v.ModuleRights
    FROM Users u CROSS JOIN
         (VALUES (1, 15), 
                 (2, 1), 
                 (4, 3), 
                 (8, 7)
         ) v(ModuleID, ModuleRights);

Upvotes: 7

chandni patel
chandni patel

Reputation: 1

insert multiple rows using select statment

insert into tbl_teacher
    (fName,
    lName,
    email,
    cNo)
    select s.fName,
           s.lName,
           s.email,
           s.cNo 
    from tbl_student s

Upvotes: -1

vtuhtan
vtuhtan

Reputation: 1066

INSERT INTO SomeTable (UserID, ModuleID, ModuleRights) 
SELECT u.UserID, u.ModuleID, u.ModuleRights FROM Users u;

If your ModuleID and ModuleRights are not part of the users table then insert nulls or dummy values and replace on needed condition.

Upvotes: 0

Related Questions