CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3383390
user3383390

Reputation: 211

Add an incremental number sequence in SQL INSERT SELECT Query

I have a query to insert values from one table to another table as below.

 INSERT into Workflow_CustomFormColumns     
    (CustomFormId
     ,ColumnNumber
     ,ColumnTitle
     ,ColumnType
     ,ColumnListValues
     ,IsRequired,ColumnWidth
     ,Calculation)
 SELECT ColumnNumber
    ,ColumnTitle
    ,ColumnType
    ,ColumnListValues
    ,IsRequired
    ,ColumnWidth
    ,Calculation
 FROM Workflow_CustomFormTypeColumns
 WHERE CustomFormTypeId=66 
    and ColumnNumber>43

Now for the CustomformID in WorkFlow_CustomformColumns I need to add values which increment from 250 to 300

Upvotes: 2

Views: 4297

Answers (2)

Deepshikha
Deepshikha

Reputation: 10264

If CustomFormId is identity column then we can use DBCC CHECKIDENT to specify a new seed value by using RESEED new_reseed_value as below before Inserting data:

DBCC CHECKIDENT ('dbo.Workflow_CustomFormColumns', RESEED, 250);

Check DEMO here.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can do this with row_number() in the insert:

INSERT into Workflow_CustomFormColumns(CustomFormId, ColumnNumber, ColumnTitle,
              ColumnType, ColumnListValues, IsRequired, ColumnWidth, Calculation
             )
    SELECT 249 + row_number() over (order by (select NULL)) as CustomerFormId,
          ColumnNumber, ColumnTitle, ColumnType, ColumnListValues, IsRequired, 
          ColumnWidth, Calculation
    from  Workflow_CustomFormTypeColumns
    where CustomFormTypeId = 66 and ColumnNumber > 43;

However, it is hard for me to think of a situation where you would really want this. Instead, yo would typically make CustomerFormId and identity column and let the database assign unique numbers to the id.

Upvotes: 8

Related Questions