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'.
INSERTthe 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 toCREATEthe table, andINSERTthe data, but also when you want toSELECTfrom 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+).