sql server - SQL Declare Select Insert -

i'm trying query run on results of select.
problem select log_item201303 returns 1 result.
means insert into clause affects 1 row each time run script.
want results (600+ total) , insert of them, row row.

declare @charkey int, @charname varchar(16), @item int  select @charkey = char_key, @charname = name, @item = itemnum log_item201303  (        itemnum = 14317 or  itemnum = 14318 or  itemnum = 15478 or  itemnum = 15479 or  itemnum = 14301 or  itemnum = 14302 or  itemnum = 15476 or  itemnum = 15477 or  itemnum = 15018 or  itemnum = 15019 or  itemnum = 15020 or  itemnum = 15021 or  itemnum = 15022 or  itemnum = 15023 or  itemnum = 15024 or  itemnum = 15025 or  itemnum = 14437 or  itemnum = 14438 or  itemnum = 15656 or  itemnum = 15657 or  itemnum = 15658 or  itemnum = 15659 or  itemnum = 15660 or  itemnum = 15661 or  itemnum = 15662 or  itemnum = 15663 ) , (kind = 133) , (convert(varchar, occur_time,111) < '2013/03/22')  select @charkey, @charname, @item  insert game.dbo.mail_item_table (itemnumber, iteminfo, receivedate) values (@item, (select convert(binary(16), reverse(convert(binary(16), @item)))), null)  insert game.dbo.mail_message_table (message) values ('automated message admin.')  insert game.dbo.mail_list_table (receivercharkey, maillistindex, mailitemindex, mailmessageindex, sender, receiver, senddate) values  (@charkey, (select top 1   maillistindex+1 last_entry            game.dbo.mail_list_table           sender = 'send0r'  order maillistindex desc), (select top 1   mailitemindex last_entry            game.dbo.mail_item_table  order       mailitemindex desc), (select top 1   mailmessageindex last_entry            game.dbo.mail_message_table  order       mailmessageindex desc),  'send0r', '@charname', getdate()) 

the main issue select individual variables - ever works single row - since each variable can hold single value. also: sql in general, should avoid "row-by-row" (also known rbar - row-by-agonizing-row) approach - it's bad design, , kills off performance might set-based approaches. avoid whenever possible!

what need instead insert data target tables directly select - not using variables in between... or if need use same data multiple inserts (as looks in case here), you'll need store results initial select in table variable (not individual variables each row)

so try this:

-- define table variable hold data declare @temporarydata table (charkey int, charname varchar(16), item int)  -- initial select - insert data table variable     insert @temporarydata(charkey, charname, item)    select char_key, name, itemnum    log_item201303    ( ..... )   -- insert data table variable first table     insert game.dbo.mail_item_table(itemnumber, iteminfo, receivedate)    select         item,         convert(binary(16), reverse(convert(binary(16), item)))),         null    @temporarydata  -- unrelated table variable ...     insert game.dbo.mail_message_table(message) values('automated message admin.')  -- insert data table variable yet table...     insert game.dbo.mail_list_table(receivercharkey, maillistindex, mailitemindex, mailmessageindex, sender, receiver, senddate)   select       charkey,      (select top 1 maillistindex+1 last_entry       game.dbo.mail_list_table       sender = 'send0r'       order maillistindex desc),      (select top 1 mailitemindex last_entry       game.dbo.mail_item_table       order mailitemindex desc),      (select top 1 mailmessageindex last_entry       game.dbo.mail_message_table       order mailmessageindex desc),      'send0r',       charname,       getdate()    @temporarydata