CODEWITHSUNDEEP
sqlsql-server
liquidsnake786
liquidsnake786

Reputation: 447

Create table and setting identity seed using sp_executesql

Need help creating a table using the 'sp_executesql' command in sql server and setting the seed with a variable. I get the seed I want and store it in @theSeed

DECLARE @theSeed int
SELECT @theSeed = (MAX([Id]) + 1) FROM [Person]

then i want to create the table and use this seed as the identity seed in the new table

EXEC sp_executesql N'CREATE TABLE [NewPerson] (
    [PersonId] [int] IDENTITY(5000 , 1) NOT NULL
PRIMARY KEY CLUSTERED 
(
    [PersonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]'
GO

I need to replace the 5000 with the value in @theSeed. thanks

Upvotes: 0

Views: 525

Answers (2)

Indranil.Bharambe
Indranil.Bharambe

Reputation: 1498

    DECLARE @theSeed int
    SELECT @theSeed = (MAX([Id]) + 1) FROM [Person]
declare @sql varchar(1000) = 'CREATE TABLE [NewPerson] (
        [PersonId] [int] IDENTITY(' + @theSeed + ', 1) NOT NULL
    PRIMARY KEY CLUSTERED 
    (
        [PersonId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]'
    EXEC sp_executesql @sql
    GO

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10264

You can use DBCC CHECKIDENT command for this as:

--command to check the current identity value
DBCC CHECKIDENT ('NewPerson')

-- create table and reseed value with @theSeed variable:
DBCC CHECKIDENT ('NewPerson', RESEED, @theSeed)

or you can write the dynamic query as:

DECLARE @theSeed int
SELECT @theSeed = (MAX([Id]) + 1) FROM [Person]

declare @sql nvarchar(4000)
set @sql = 'CREATE TABLE [NewPerson] (
    [PersonId] [int] IDENTITY(' + cast(@theSeed as varchar) + ', 1) NOT NULL
PRIMARY KEY CLUSTERED 
(
    [PersonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]'


EXEC sp_executesql @sql
GO

--command to check the current identity value
DBCC CHECKIDENT ('NewPerson', NORESEED)

Upvotes: 1

Related Questions