October 20, 2011 at 5:55 am
When I try to execute a command below in this script it works but when i change it to being dynamic as I did below, it does not work. Why does that part not work? (FYI...I uncomment this part when testing)
Declare @Schema as varchar(100)
Declare @cmd as varchar(400)
Declare @TableNameHeader as varchar(400)
Declare @TableNameDetail as varchar(400)
Declare @SourceDB as varchar(100)
DECLARE @MyTableVar TABLE
(
TableID INT,
TableName VARCHAR(100)
)
set @SourceDB = 'NJ_Toys_Claims'
set @Schema = @SourceDB + '.INFORMATION_SCHEMA.COLUMNS'
set @TableNameHeader = 'TRU_2010_PRICING_CLAIM_SUMMARY'
set @TableNameDetail = 'TRU_2010_PRICING_CLAIM_DETAIL2'
-- Add Tables Info for Header
Insert Into [tblTables](TableName, TableIndex, SQLAuth)
Output Inserted.TableID, Inserted.TableName Into @MyTableVar
Select @TableNameHeader, 2, 0 WHERE @TableNameHeader
Not IN (Select Tablename From tblTables)
-- Add Tables Info for Detail
Insert Into [tblTables](TableName, TableIndex, SQLAuth)
Output Inserted.TableID, Inserted.TableName Into @MyTableVar
Select Item, 2, 0 From PRGX_AS_UTILITY.dbo.DelimitedSplit8k(@TableNameDetail,',') as T WHERE T.Item
Not IN (Select Tablename From tblTables)
-- This below works....
/*
INSERT INTO tblFields (TableID, FieldName, FieldType)
SELECT t.TableID,c.COLUMN_NAME,c.DATA_TYPE
FROM @MyTableVar as t
INNER JOIN NJ_Toys_Claims.INFORMATION_SCHEMA.COLUMNS as c
ON c.TABLE_NAME = t.TableName
*/
-- This below does not work...
Set @cmd = 'INSERT INTO tblFields (TableID, FieldName, FieldType)
SELECT t.TableID,c.COLUMN_NAME,c.DATA_TYPE
FROM @MyTableVar as t
INNER JOIN ' + @Schema + ' as c
ON c.TABLE_NAME = t.TableName'
exec(@cmd)
select @cmd
October 20, 2011 at 6:02 am
I get this error msg:
Must declare the table variable "@MyTableVar".
October 20, 2011 at 6:08 am
The dynamic sql is a whole new batch. Excluding temp tables, nothing else will be visible down there. That's why it doesn't see the variable.
You can concatenate it in or use sp_executesql to pass parameters.
October 20, 2011 at 6:49 am
changed to using a temp table and that worked!
Select * Into ##tmpTableInfo From @MyTableVar
Set @cmd = 'INSERT INTO tblFields (TableID, FieldName, FieldType)
SELECT t.TableID,c.COLUMN_NAME,c.DATA_TYPE
FROM ##tmpTableInfo as t
INNER JOIN ' + @Schema + ' as c
ON c.TABLE_NAME = t.TableName'
exec(@cmd)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply