Trying to insert data into an sql server table using a variable as the table name
Has any body got any idea how I can use a variable as a table name when
trying to insert data into a table
My code uses VB to pass parameters to a SQL server 7 stored procedure which
will populate a table.
Basically all I need to know is how do you write the syntax.
CREATE PROCEDURE [INSERT_DATA]
@TABLENAME AS SYSNAME
INSERT INTO @TABLENAME
(NAME, ADDRESSS, DOB)
(@NAME, @ADDRESS, @DOB)
It will not recognize @TABLENAME as a valid table name.
Is it the syntax???
Any help at all would be great
Re: Trying to insert data into an sql server table using a variable as the table name
You need to create your SQL statement as a literal string (varchar) and run
it as an adhoc query or use the sp_executeSQL stored procedure. The latter
is the suggested method.
The query engine can reuse the always reuse your plan from a parameterized
query when the parameter values change. It is important to know that even
though the stored proc has a compliled execution plan the SQL that runs in
this proc will not. This is because the statement is created at run-time.
It will be cached in memory for reuse once it is executed for the first
time. This is trival for a simple statement as this. But, it is an important
consideration when developing more complex statments as such.
I'll use your t-sql as an example
CREATE PROCEDURE usp_InsertDataExample
DECLARE @SQLAdHoc varchar(1000)
DECLARE @ReturnValue int
SET @SQLAdHoc = 'INSERT INTO ' + @TableName + ' (Name, DOB, Address)'
SET @SQLAdHoc = @SQLAdHoc + ' VALUES (@P1, @P2, @P3)'
EXECUTE @ReturnValue = sp_executeSQL @SQLAdHoc, '@P1 varchar(50), @P2 datetime,
@P3 varchar(150)', @Name, @DOB, @Address
"Julian Pickard" <firstname.lastname@example.org> wrote:
>Has any body got any idea how I can use a variable as a table name when
>trying to insert data into a table
>My code uses VB to pass parameters to a SQL server 7 stored procedure which
>will populate a table.
>Basically all I need to know is how do you write the syntax.
>CREATE PROCEDURE [INSERT_DATA]
>@TABLENAME AS SYSNAME
>INSERT INTO @TABLENAME
> (NAME, ADDRESSS, DOB)
> (@NAME, @ADDRESS, @DOB)
>It will not recognize @TABLENAME as a valid table name.
>Is it the syntax???
>Any help at all would be great
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center