Filling a table variable with sp_executeSQL 1

[Viruland]

I want to fill a table variable with data from different tables.
I always get the following error code "Must declare the variable '@outputtable'.".
Can someone tell me what I'm doing wrong here?



declare @sourcetable sysname
declare @outputtable table (IDNr nchar(12), Description nvarchar(150))
declare counter int
declare @sql as nvarchar(2000)

set counter = 0

while counter < 3
BEGIN

IF COUNTER = 0
BEGIN

set @sourcetable = 'tbl_SalesComp'

END
ELSE IF COUNTER = 1
BEGIN

set @sourcetable = 'tbl_SalesExt'

END
ELSE IF COUNTER = 2
BEGIN

set @sourcetable = 'tbl_SalesSpec'

END


set @sql = 'INSERT INTO @tbl select IDNr, Description from ' + @sourcetable

exec sp_executesql @sql, N'@tbl table (IDNr nchar(12), Description nvarchar(150))', @outputtable

counter = counter + 1

END


Live fast, die young and leave a beautiful corpse behind.

 

[bborissov]

You can't use table variable for this. You must use a Temp table. Dynamic SQL is executed in other JOB where you variables are not visible. Also you don't need such contruction here, you just could do:

declare @outputtable  table (IDNr nchar(12), Description  nvarchar(150))

INSERT INTO @outputtable (IDNr, Description)
SELECT IDNr, Description from tbl_SalesComp
UNION ALL
SELECT IDNr, Description from tbl_SalesExt
UNION ALL
SELECT IDNr, Description from tbl_SalesSpec

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP