0

I am trying to create a procedure on sql where I will create a new table based on c# variables. And I will transfer data from old table to new table. I will call the procedure from c# and the variables will be set from there.

I tried many different commands, but each time I got an error related to variables on sql.

    CREATE PROCEDURE ab_createtable
AS
DECLARE @newtable as NVARCHAR(50)
DECLARE @oldtable as NVARCHAR(50)

BEGIN

CREATE TABLE @newtable

(
    [No] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Surname] [nvarchar](50) NULL,
    )
    SET IDENTITY_INSERT [dbo].[@newtable] ON

    INSERT INTO [dbo].[@newtable](No, Name, Surname)
    SELECT No, Name, Surname FROM [dbo].[@oldtable]

    SET IDENTITY_INSERT [dbo].[@newtable] OFF

END

Result:

Incorrect syntax near '@newtable'.
AnlBozkrt
  • 1
  • 5
    You would have to use dynamic sql for this. But to be honest this sounds like an [XY Problem](https://xyproblem.info/) to me. Why do you need to make copies of your tables often enough that you need a procedure to do it? – Sean Lange Jul 06 '22 at 13:10
  • 1
    Parameters can't be used for table names. If you write dynamic SQL consider using [QUOTNAME](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql?view=sql-server-ver16) to help protect against SQL injection. – Crowcoder Jul 06 '22 at 13:16
  • I am developing a project that will work with a new table each month that contains the previous month's immutable information (example employee name) and variable information (example employee salary). For this reason, I needed such a procedure to create a new table at the end of each month. – AnlBozkrt Jul 06 '22 at 13:18
  • 1
    Why not `INSERT` the data into a table with a column that denotes the date is effectively for. You might also then want to consider partitioning said table. Otherwise not only do you need to use dynamic SQL to `CREATE` the table, and `INSERT` the data, but also when you want to `SELECT` from it. Take it from someone that works at a business with that kind of design, it's a nightmare to work with (fortunately I've been slowly migrating us to a more normalised format as we get onto instances running 2016 SP1+). – Thom A Jul 06 '22 at 13:19
  • 2
    Yes a new table each month is a big red flag. That is almost always a less than ideal design decision. Instead of a new table you could add a column for the date. Consider how you are going to query against a dozen different tables each year. In just 5 years you have more than 50 tables to deal with. I have seen this pattern many times and it has not been a good approach in any of those times. – Sean Lange Jul 06 '22 at 13:23
  • instead of creating a table for each month, consider creating an auditor table that will store the old values along with the update datetime, and keep the original table with the newest values only. or use versioning technique. – iSR5 Jul 06 '22 at 15:04

0 Answers0