"j" <j@discussions.microsoft.com> wrote in message
news:45EC3EC4-08D4-41D5-83A3-A8D4A855D833@microsoft.com...
> Hello,  I would truly appreciate any help.   I am stuck on how to use a
> value
> returned from a query, assign it to a variable and used it with the INSERT
> INTO clause to insert values from one table to an object name dynamically
> rendered to a variable.  Is that possible?
>
> For example:
>
> --need to used the object name returned in the result set to the variable
> DECLARE @tblname nvarchar(800)
> set @tblname = N'select table_name FROM INFORMATION_SCHEMA.TABLES WHERE
> TABLE_NAME = OBJECT_NAME(1301579675)'
>
> --When I run execute sp_executesql @tblname, I get the correct table name
> as
> a result.
>
> --Now this is the tricky part (for me anyway):
> INSERT INTO @tblname
> SELECT  B.col002, B.col003, B.col004, B.col005, B.col006, B.col007,
> B.col008, B.col009, B.col010, B.col011, B.col012, B.col013, Bcol014
>
> FROM INFORMATION_SCHEMA.TABLES A, VIEW_TABLE_LOAD B
> WHERE A.TABLE_NAME = B.TABLE_NAME
> AND  B.IND = 1
>
> --When I try to use the variable @tblname with the INSERT INTO clause, it
> errors with the following statement:
> Server: Msg 137, Level 15, State 2, Line 5
> Must declare the variable '@tblname'.
>
> Thank you very much!
>
>

"David Portas" wrote:

> "j" <j@discussions.microsoft.com> wrote in message
> news:45EC3EC4-08D4-41D5-83A3-A8D4A855D833@microsoft.com...
> > Hello,  I would truly appreciate any help.   I am stuck on how to use a
> > value
> > returned from a query, assign it to a variable and used it with the INSERT
> > INTO clause to insert values from one table to an object name dynamically
> > rendered to a variable.  Is that possible?
> >
> > For example:
> >
> > --need to used the object name returned in the result set to the variable
> > DECLARE @tblname nvarchar(800)
> > set @tblname = N'select table_name FROM INFORMATION_SCHEMA.TABLES WHERE
> > TABLE_NAME = OBJECT_NAME(1301579675)'
> >
> > --When I run execute sp_executesql @tblname, I get the correct table name
> > as
> > a result.
> >
> > --Now this is the tricky part (for me anyway):
> > INSERT INTO @tblname
> > SELECT  B.col002, B.col003, B.col004, B.col005, B.col006, B.col007,
> > B.col008, B.col009, B.col010, B.col011, B.col012, B.col013, Bcol014
> >
> > FROM INFORMATION_SCHEMA.TABLES A, VIEW_TABLE_LOAD B
> > WHERE A.TABLE_NAME = B.TABLE_NAME
> > AND  B.IND = 1
> >
> > --When I try to use the variable @tblname with the INSERT INTO clause, it
> > errors with the following statement:
> > Server: Msg 137, Level 15, State 2, Line 5
> > Must declare the variable '@tblname'.
> >
> > Thank you very much!
> >
> >
>
> Why don't you know the name(s) of your table(s)? Even stranger, why would
> you know the object ID but not the name. This looks suspiciously like the
> results of a poorly thought out design.
>
> The quick and dirty fix is called dynamic SQL:
> http://www.sommarskog.se/dynamic_sql.html
>
> The *solution* on the other hand is perhaps to rethink your requirements.
>
> --
> David Portas
> SQL Server MVP
> --
>
>
>