CODEWITHSUNDEEP
sqlsql-server
Syntax_MM
Syntax_MM

Reputation: 53

Stored procedure insert into table not working due to identifier not found

I'm trying to insert all records from tableA to tableB. TableA exists, TableB does not.

Here is my stored procedure. This code works but it's limited to a fixed table name tableB:

USE [myDatabaseName]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[myStoreProcedureFileName]
AS
BEGIN
    SELECT * 
    INTO tableB 
    FROM tableA
END

However, I want to make tableB as a variable so I can pass it from C# code, this doesn't work, please help:

ALTER PROCEDURE [dbo].[myStoreProcedureFileName]
    @tableName varchar(32)
AS
BEGIN
    SELECT * 
    INTO @tableName 
    FROM tableA
END

Please help - why is SQL Server not recognizing @tableName in the select line? Solutions?

Upvotes: 0

Views: 564

Answers (2)

Dmitry Kolchev
Dmitry Kolchev

Reputation: 2216

You must use dynamic SQL

declare @sql nvarchar(max);

set @sql = N'select * into ' + @tableName + N' from tableA';
exec sp_executesql @sql;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You need to use dynamic SQL:

ALTER PROCEDURE [dbo].[myStoreProcedureFileName] (
    @tableName varchar(32)
) AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = 'SELECT * into @tableName from tableA';
    SET @sql = REPLACE(@sql, '@tableName', @tableName);
    EXEC sp_executesql @sql;
END;

Parameters can only replace constants in a SQL statement. They cannot replace identifiers, operators, function names, or keywords.

Upvotes: 2

Related Questions