How to perform insert for each selected row from database

Question

I am trying to make stored procedure that:
- Get list of int rows

select ItemId from Items -- this returns: 1,2,3,4,5,6

• In the second part of procedure I have to add row in another table for each of selected number.
  Something like:

  foreach ItemId in previous result insert into table (ItemIdInAnotherTable) values (ItemId)
  UPDATE
  
  I miss one important part from question. In another part of procedure when I am inserting selected items in another table need to insert a few more columns. Something like this:

  insert into dbo.ItemsNotificator (UserId,ItemId) (13879, (select ItemId from Items))

So it's not one column. Sorry for confusion :(

Answer 1

Edit : Assuming that the table [table] already exists, and if User is a constant, then do like so:

INSERT INTO [table](UserId, ItemIdInAnotherTable) 
   SELECT 13879, ItemId 
   FROM Items;

If UserId comes from another table entirely, you'll need to figure out what relationship you need between UserId and ItemId. For instance, if all users are linked to all items, then it is:

INSERT INTO [table](UserId, ItemIdInAnotherTable) 
   SELECT u.UserId, i.ItemId 
   FROM Items i CROSS JOIN Users u;

If table [table] does NOT already exist, then you can use SELECT INTO, and specify a new table name (e.g. a #temp table stored in tempdb)

SELECT u.UserId, i.ItemId 
   INTO #tmpNewTable
   FROM Items i CROSS JOIN Users u;

The columns in the newly created table will have the names UserId and ItemId and have the same types.

Answer 2

use INSERT INTO..SELECT statement

INSERT INTO ItemIdInAnotherTable (ItemId)
SELECT ItemId 
FROM   Items

Answer 3

That is exactly what a normal insert command does. Just do something like this:

insert into Table (ItemID) 
    select ItemID from Items;

Answer 4

Looks simple to me:

 INSERT INTO ItemIdInAnotherTable (ItemId)
 SELECT ItemId from Items