CODEWITHSUNDEEP
sqlsql-server
Hazim
Hazim

Reputation: 69

Declare temp table and insert into in query

I try to call declare temp table in my query but it say

"Must declare the table variable "@MDLTable"."

Here my coding:

DECLARE @dbname AS NVARCHAR(50);
SELECT @dbname = DB_NAME(); 
PRINT @dbname;
DECLARE @sql NVARCHAR(1000) ;
DECLARE @MDLTable AS TABLE(MDLID BIGINT, MDLRLVR INT)

SET @sql = 'INSERT INTO ' + @MDLTable + ' (MDLID, MDLRLVR) 
SELECT DISTINCT a.MDLID, a.MDLRLVR FROM ' + @dbname + '.dbo.EUSYSRSRRL a 
INNER JOIN  ' + @dbname + '.dbo.EUSYSIROE b ON a.MDLID = b.MDLID 
INNER JOIN ' + @dbname + '.dbo.EUSYSEAPML c ON b.EAPMLID = c.EAPMLID 
WHERE c.REEID = ' + CONVERT(nvarchar(50),6) + ''
exec (@sql);

Upvotes: 0

Views: 2639

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this by using insert . . . exec:

SET @sql = '
SELECT DISTINCT a.MDLID, a.MDLRLVR
FROM ' + @dbname + '.dbo.EUSYSRSRRL a JOIN
     ' + @dbname + '.dbo.EUSYSIROE b
     ON a.MDLID = b.MDLID JOIN
     ' + @dbname + '.dbo.EUSYSEAPML c
     ON b.EAPMLID = c.EAPMLID 
WHERE c.REEID = ' + CONVERT(nvarchar(50), 6) + '';

INSERT INTO @MDLTable (MDLID, MDLRLVR) 
    exec(@sql);

Upvotes: 1

Related Questions