In my sp,I want to get some data from a table which name is dynamic
,and insert the data to a table varialbe,in other words,look like
below:
StoredProcedure
(
   DECLARE @TABLENAME VARCHAR(255)
   DECLARE @MyTABLE TABLE (ID INT,NAME VARCHAR(255),AGE INT)

   INSERT INTO
        @MyTABLE
   SELECT
       ID,
       NAME,
       AGE
   FROM
      @TABELNAME
)

how to do this?thanks

John
Well you are goimg to use dynamic sql, try this

declare @t  varchar(10)

set @t ='Orders'

exec('declare @table table (col1 int)
insert into @table select orderid from '+ @t+'
select * from @table')


Note ; a declaration of the table variable must be in the scope of the EXEC
command, I'd consider using
sp_executesql system stored procedure

See an example

declare @sql nvarchar(1000),
@col sysname,
@orderid int

select @col='OrderID', @orderid=10248

set @sql = 'select * from Northwind..Orders where '+@col+'= @ordid'
exec sp_executesql @sql, N'@ordid int',@orderid





Show quoteHide quote

I want to returun the table variable as a result set at the end of
stored procedure,your way just insert it to a table variable in a short
scope,can't return to outside,anyway,still need to say thanks to you.

StoredProcedure
(
   DECLARE @TABLENAME VARCHAR(255)
   DECLARE @MyTABLE TABLE (ID INT,NAME VARCHAR(255),AGE INT)

   INSERT INTO
        @MyTABLE
   SELECT
       ID,
       NAME,
       AGE
   FROM
      @TABELNAME


  SELECT
        ID,
        NAME,
        AGE
  FROM
       @MyTable
)

I have to use tempoary table to solve this problem

Yes.

Stored procedure would:
1. Create the temporary table #MyTable with the columns you need.
2. Use dynamic sql to fill the temporary table.
set @SQL = 'INSERT INTO #MyTABLE SELECT ID, NAME, AGE FROM ' + @TABELNAME
exec sp_executesql @SQL
3. Use the data as needed in the temporary table.
4. If you need to return the data as a result set, select * from #MyTable
5. Drop #MyTable

Hope that helps,
Joe

Show quoteHide quote

I hope I can use table variable but temporary table,because table
variable is more efficent than temporary table,but I couldn't:(

Sorry, I don't know of a way to pass a table variable between stored
procedures.  And, doing an "insert into @MyTable exec spGetData" is not
allowed.

If it was a static table name, you could use a user-defined function to
return a table variable.  However, a udf cannot execute dynamic sql so that's
not an option in your case.

I think the only way this will work is to use a temporary table.  If this
stored procedure is going to be executed a lot, you might be better off
having a permanent table where the primary key is the server process id
(@@spid).  Simply delete from the table where spid = @@spid to make sure
there is no old data present, execute dynamic sql that fills in the data
(either in the stored procedure or call another sp to do it), use the data as
needed, delete the data for the @@spid.

If sequence of rows is important, try:
PK int identity, primary key, clustered
spid smallint default @@spid
sequence int
datacol1 ...
datacol2...
etc.

The primary key is auto assigned and data is inserted at the end of the data
pages. Write a trigger to fill in the sequence number.  Create a unique
constraint on spid and sequence with a low fill factor say 50%.  This fill
factor will have to be optimized to your environment.  If there is heavy
usage with a lot of rows, it may have to be as low as 10%.  Ideally, you want
to have room for quick inserts without SQL Server having to split the index
page a lot as that's a costly action.

Hope that helps,
Joe

Show quoteHide quote

John Smith (debu***@gmail.com) writes:
Since you are inserting from dynamic SQL, you can forget about table
variables.

As for which is the most effecient, that depends. Sometimes, it's table
variables, sometimes it's temp tables.

Table variables do not have statistics. This means that they cannot
cause recompilation. Temp tables has statistics, and this can lead to
recompilation. Recompilation can be expensive, and prolong the execution
timef from 200 ms to 1200 ms, which the users very painfully will notice.
But recompilation can also cut the execution time from two minutes to
1200 ms.

Generally, the more data you expect in your temporary table/table variable,
the more likely is that the statistcs are needed for a good query plan,
and thus that you should pick a temp table.

Finding the right record
CHARINDEX in CASE Within SELECT Statement
Default Value Calculation
Deadlock updating different rows
No retrieving dupicate rows