How to pass the table name and database name through variable in sql query
How to pass the table name and database name through variable in sql query
i have written below query for dynamically create the table and insert the records from source table.
Declare @Sql Varchar(Max)
Declare @x int
declare @value varchar(100),@schema varchar(100)='aaa'
Declare @f1 Varchar(100)
Declare @table Table(Idt Int Identity(1,1),grp_id numeric(18,0),id int)
Insert Into @table Select Distinct grp_id,0 from abc..table1
Select @x = Max(Idt) from @table
select @value=(select id from cde..table2 where Shortname='+@schema+')
While @x>0
Begin
Select @f1= grp_id From @table Where Idt = @x
Set @Sql =
'If Exists( Select 1 from sys.objects where name = ''condition_'+@f1+''' And Type_Desc = ''USER_TABLE'')
Insert Into condition_'+@f1+ ' Select * from abc..table1
Where grp_id = '''+@f1+'''
Else
Select * Into condition_'+@f1+' from abc..table1
Where grp_id = '''+@f1+'''
alter table condition_'+@f1+' add id int
update condition_'+@f1+' set id = '+@value+'
'
Set @x = @x-1
--print @sql
Exec (@Sql)
End
how to pass the table name , database name through variable for this query because this query is used for 5 tables so every time change the table name.
so will use the variable and pass the value for table name and database name only.
please modify this query.
Declare @Sql Varchar(Max)
Declare @x int
declare @value varchar(100),@schema varchar(100)
SET @schema='aaa'
Declare @f1 Varchar(100)
Declare @table Table(Idt Int Identity(1,1),grp_id numeric(18,0),id int)
DECLARE @TABLENAME_2 NVARCHAR(10)
DECLARE @TABLENAME_1 NVARCHAR(10)
SET @TABLENAME_2 ='abc..table1'
Insert Into @table Select Distinct grp_id,0 from abc..table1
Select @x = Max(Idt) from @table
select @value=(select id from cde..table2 where Shortname='+@schema+')
While @x>0
Begin
Select @f1= grp_id From @table Where Idt = @x
SET @TABLENAME_1='EligPop_' + @f1
Set @Sql ='If Exists( Select 1 from sys.objects where name = '+ @TABLENAME_1 +' And Type_Desc = ''USER_TABLE'')
Insert Into EligPop_'+@f1+ ' Select * from '+@TABLENAME_2+' Where grp_id = '''+@f1+'''
Else Select * Into EligPop_'+@f1+' from '+@TABLENAME_2+' Where grp_id = '''+@f1+'''
alter table '+ @TABLENAME_1 +' add id int
update '+ @TABLENAME_1 +' set id = '+@value+''
Set @x = @x-1
print @sql
--Exec (@Sql)
End