| Author |
Topic |
|
abenitez77
Yak Posting Veteran
53 Posts |
 Posted - 2011-10-20 : 07:53:02
|
| 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?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 HeaderInsert 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 DetailInsert 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 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-10-20 : 07:57:42
|
can you specify more clear "it does not work"?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-10-20 : 07:59:45
|
google this: sql server using table var in dynamic sql
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
abenitez77
Yak Posting Veteran
53 Posts |
Posted - 2011-10-20 : 08:01:31
|
| I get this error msg:Must declare the table variable "@MyTableVar". |
|
|
|
abenitez77
Yak Posting Veteran
53 Posts |
Posted - 2011-10-20 : 08:49:40
|
| 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) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-20 : 09:15:02
|
| i dont think you need dynamic sql for this. based on db on which you execute it it should take column info from relevant information_schema view itself.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
abenitez77
Yak Posting Veteran
53 Posts |
Posted - 2011-10-20 : 15:14:40
|
| actually I do because @Schema in my string has the dbname, where the table resides. It is not on the same db where I kick the script off from. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-21 : 00:39:27
|
| oh ok. why are looking in another dbs catalog view from code executed in your db? Can i ask purpose of this?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
abenitez77
Yak Posting Veteran
53 Posts |
Posted - 2011-10-21 : 06:50:41
|
| The code looks at another db to pull the table columns and column type and saves that info in my main db where an application uses certain tables with info about the data and the column info. There is also other info saved in tables that is used by the app. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-21 : 08:19:40
|
| are you trying to create tables based on other db table for data export?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
abenitez77
Yak Posting Veteran
53 Posts |
Posted - 2011-10-21 : 08:56:21
|
| No, just getting info about tables that a don net app uses. The app has the system tables for that app and it houses info about a project. The project uses different db's and tables within that db. the app needs to know what the column names and column types are and the app also uses the data from those tables. |
|
|
|
|
dynamic SQL