CODEWITHSUNDEEP
sqlsql-servert-sqlvariablesddl
Ken
Ken

Reputation: 349

Incorrect syntax when working with a variable in IDENTITY

I'm trying to create a new table with a primary key value that is a continuation of a previous table.

My code is:

DECLARE @Table1_NextKey INT

SELECT @Table1_NextKey = MAX(id) + 1 
FROM [Copy of Table1]

CREATE TABLE [dbo].Table1
(
    [ID] [int] NOT NULL IDENTITY(@Table1_NextKey, 1) 
        CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED,
    [PLAN] [nvarchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

But I get this error:

Msg 102, Level 15, State 1, Line 24
Incorrect syntax near '@Table1_NextKey'

Is there a way to get the Create Table to work with the variable?

Upvotes: 0

Views: 722

Answers (3)

Charlieface
Charlieface

Reputation: 71578

You are going about this the wrong way.

What you are clearly trying to do, is copy a table and then you would like to continue the identity values.

In this case, do not declare the seed value differently in the CREATE TABLE, just manually set it afterwards:

CREATE TABLE [dbo].Table1
(
    [ID] [int] NOT NULL IDENTITY(1, 1) 
        CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED,
    [PLAN] [nvarchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

-- Do some copying code here

DECLARE @Table1_NextKey INT =
(
    SELECT @Table1_NextKey = MAX(id)  -- not + 1 
    FROM Table1
);

DBCC CHECKIDENT (Table1 RESEED, @Table1_NextKey) WITH NO_INFOMSGS;

Upvotes: 2

Stu
Stu

Reputation: 32609

You can only use literal values for identity, you'll need to dynamically construct your create statement, as follows

declare @sql nvarchar(max)=Concat(N'
CREATE TABLE [dbo].Table1(
  [ID] [int] NOT NULL IDENTITY(', @Table1_NextKey, N', 1) CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED,
  [PLAN] [nvarchar](255) NULL) 
ON [PRIMARY]')

exec sp_executesql @sql

Upvotes: 1

Ken
Ken

Reputation: 349

Don't know why SQL Server is so weird about this but with Stu's help, I got this to work:

DECLARE @Table1_NextKey INT,
@SQL_command varchar(4000)
select @Table1_NextKey=max(id)+1 from [Copy of Table1]

set @SQL_command=
'CREATE TABLE [dbo].Table1(
    [ID] [int] NOT NULL IDENTITY(' + convert(varchar(5), @Table1_Nextkey) + ', 1) CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED,
    [PLAN] [nvarchar](255) NULL) 
ON [PRIMARY] 
GO'

Upvotes: 0

Related Questions