Insert dynamic SQL Values into Temp Table
-
Hi, I need to insert Dynamic SQL Values into Temp table, but I am able to insert values into Global Temp table but I don't know how to insert Global Temp table values into Temp table back again. Any help would be greatly helpful. Any sort of implementation to insert Dynamic SQL values into Temp table is fine, directly or indirectly is fine. Here is how I am able to insert the Dynamic SQL Values into Global temp table, but not able to do the same for the Temp table, even if I can insert from Global Temp table to Temp table is also fine. Any help would be greatly helpful - thanks in advance buddies.
DECLARE @sqlCommand varchar(max) = 'SELECT TABLE_NAME INTO ##TableNames FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='''+ @schema + ''''
EXEC (@sqlCommand) EXEC ('SELECT \* FROM \[##TableNames\] ')Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi, I need to insert Dynamic SQL Values into Temp table, but I am able to insert values into Global Temp table but I don't know how to insert Global Temp table values into Temp table back again. Any help would be greatly helpful. Any sort of implementation to insert Dynamic SQL values into Temp table is fine, directly or indirectly is fine. Here is how I am able to insert the Dynamic SQL Values into Global temp table, but not able to do the same for the Temp table, even if I can insert from Global Temp table to Temp table is also fine. Any help would be greatly helpful - thanks in advance buddies.
DECLARE @sqlCommand varchar(max) = 'SELECT TABLE_NAME INTO ##TableNames FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='''+ @schema + ''''
EXEC (@sqlCommand) EXEC ('SELECT \* FROM \[##TableNames\] ')Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
You have to create the temp table before executing the dynamic SQL:
CREATE TABLE #TableNames (TABLE_NAME sysname NOT NULL);
DECLARE @sqlCommand nvarchar(2000) = N'INSERT INTO #TableNames (TABLE_NAME) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schema';
EXEC sp_executesql @sqlCommand, N'@schema nvarchar(128)', @schema = @schema;
SELECT * FROM #TableNames;
DROP TABLE #TableNames;
You also need to avoid string concatenation, otherwise you'll introduce a SQL Injection[^] vulnerability. sp_executesql (Transact-SQL) | Microsoft Docs[^] But in this example, you don't need dynamic SQL at all.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
You have to create the temp table before executing the dynamic SQL:
CREATE TABLE #TableNames (TABLE_NAME sysname NOT NULL);
DECLARE @sqlCommand nvarchar(2000) = N'INSERT INTO #TableNames (TABLE_NAME) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schema';
EXEC sp_executesql @sqlCommand, N'@schema nvarchar(128)', @schema = @schema;
SELECT * FROM #TableNames;
DROP TABLE #TableNames;
You also need to avoid string concatenation, otherwise you'll introduce a SQL Injection[^] vulnerability. sp_executesql (Transact-SQL) | Microsoft Docs[^] But in this example, you don't need dynamic SQL at all.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
