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" <email@example.com> 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
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL