while loop - T-SQL: Update temp table using "from variable" query -

i have linked server connecting lotus notes database source. destination ms sql database.

i have 2 temp tables. first temp table pulling in table names linked server. there, want record count each table , store value second temp table beside table name.

i having trouble trying run loop or cursor each table name , updating second temp table record count each table name.

right getting error "incorrect syntax near 'execute'". set record_count = execute(@sqlcommand)

declare @db_tables table ( table_cat varchar(1500), table_schem varchar(1500), table_name varchar(1500), table_type varchar(1500), remarks varchar(1500) )   declare @temp_table table ( table_name varchar(1500), record_count varchar(255), drop_script varchar(1500), update_script varchar(1500) )  --load initial data linked server database insert @db_tables exec sp_tables_ex [linked_server_db]  --load table name stored procedure insert @temp_table (table_name) select table_name @db_tables   --select * @temp_table     --variable hold each table name in loop or cursor declare @tbl_name varchar(1500) --declare @sqlcommand varchar(1500)   declare cur cursor select table_name @db_tables open cur  --loop through each table name first temp table --then update second temp table (@temp_table) record count fetch next cur @tbl_name  while @@fetch_status = 0 begin  declare @sqlcommand varchar(1500) --query used record count frist temp table (@db_tables) set @sqlcommand = 'select count(*) '+@tbl_name  update @temp_table  set record_count = execute(@sqlcommand)  end close cur deallocate cur    select * @temp_table 

it's not easy use table variables execute because dynamic sql executed in different context , doesn't see variable , can't assign results execute way.

you can either insert results table variable syntax:

insert @temp_table  execute ('select ' + @tbl_name + ', count(*) ' + @tbl_name ...) 

or use temp. tables, since can refer them inside dynamic sql, like:

create table #temp_table  ( table_name varchar(1500), record_count varchar(255), drop_script varchar(1500), update_script varchar(1500) ) ... execute('update #temp_table set record_count = (select count(*) '         +@tbl_name+') table_name = '''+@tbl_name+''')