I am designing a SQL Server 2008 R2 query.
If I used string concatenation to insert into table, it does not work.
DECLARE @s1 varchar(MAX);
DECLARE @s2 varchar(MAX);
DECLARE @s3 varchar(MAX);
DECLARE @s4 varchar(MAX);
SET @s1 = 'SELECT a.id, b.name as new_name, a.value FROM ['
SET @s2 = '].[dbo].[table1] as a, '
SET @s3 = 'a_temp_table as b ' -- a_temp_table is a table variable. No
matter I put "@" or "#" in front of a_temp_table, it doe snot work.
SET @s4 = 'WHERE a.id = b.id and a.address = b.address '
INSERT INTO [dbo].[table2] **nothing is inserted**
EXEC(@s1 + @my_database_name + @s2 + @s3 + @s4) **this query return
nothing**
I need to access different databases at each iteration in a loop so I prefer
string concatenation.
This is the output from "print all string "
INSERT INTO [dbo].[table2]
SELECT a.id, b.name as new_name, a.value
FROM [@my_database_name].[dbo].[table1] as a, a_temp_table as b
WHERE a.id = b.id and a.address = b.address
It works if I change it to :
INSERT INTO [dbo].[table2]
SELECT a.id, b.name as new_name, a.value
FROM [@my_database_name].[dbo].[table1] as a, @a_temp_table as b
WHERE a.id = b.id and a.address = b.address
But, in string format, I got error:
Must declare the table variable "@a_temp_table".
