Dynamic query on db table names

• scottichrosaviakosmos

  SSCommitted

  Points: 1889

  More actions

  March 9, 2015 at 10:31 am

  #398148

  I have query which is used to dynamically insert value but not working.

  we are trying to get all table names and insert dynamically to all tables by looping through table names.

  declare @a varchar(max),@i int;

  declare @table table(rno int, name varchar(max))

  declare @b-2 varchar(max)

  insert into @table

  select row_number() over( order by table_name) rno, table_name from INFORMATION_SCHEMA.tables

  declare @tblname varchar(max)

  declare @cnt int

  set @cnt=(select count(1) from @table)

  while (@i<=@cnt)

  begin

  set @tblname=(select name from @table where rno=@i)

  set @b-2= 'insert into'+' '+ @tblname+ ' '+ 'select * from '+' '+ @tblname

  set @i=@i+1

  end

  execute sp_sqlexec @b-2

• DouglasH

  SSCommitted

  Points: 1669

  More actions

  March 9, 2015 at 1:24 pm

  #1782690

  You need to debug this sql using standard techniques. I suggest that the first thing you do is find out what the value of @i is in your while loop.

  Then find out if @tblname is being populated, then think about what your @b-2 sql is really going to do