2

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
Eduard Pashchuk
  • 77
  • 8
  • Possible duplicate of [SQL Server SELECT into existing table](http://stackoverflow.com/questions/4101739/sql-server-select-into-existing-table) – CiucaS Feb 17 '16 at 12:09

3 Answers3

7

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);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
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.

vtuhtan
  • 1,056
  • 7
  • 18
-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
Casey
  • 12,070
  • 18
  • 71
  • 107
chandni patel
  • 1
  • Downvoted because this is too similar to vtuhtan's answer which was submitted 16 minutes earlier. The answer is also poorly formatted, but I wouldn't downvote for that. – Dan Bracuk Feb 17 '16 at 12:50