Bulk insert with unique sequence number

Question

Post reply

Bulk insert with unique sequence number

loveleshg

Old Hand

Points: 384
More actions
June 25, 2009 at 10:34 am

#97826

I need to insert multipal row using a single SQL, But one of the value in column should be unique.
On the MS SQL server how to achive this?
In oracle DB I can make use of sequenc object
i.e insert into emp_tab (ID, value) (select CAST ( seq.nextval as VARCHAR(10), value from detail_tab)
???
Is there a similar way I can use it on MS SQL DB?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

doug.williams Old Hand Points: 390 More actions · Answer 1

If you can add a column to the table that you are bulk inserting as a Identity

[ID] [int] IDENTITY(1,1) NOT NULL

This will act in the same way as a sequence from Oracle, except you do not have to specify the nextval call. The engine will increment it at insert. You don't have to reference it at insert.

BTW the (1,1) indicated the starting integer and the increment value. You could start with negative numbers for extremely large tables.

Maybe that will help.

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

In SQL Server you can handle this with ROW_NUMBER function:

INSERT INTO emp_tab (

ID,

value

)

SELECT

CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as VARCHAR(10)),

value

FROM detail_tab

Don't be confused by the "(SELECT 1)". It's just a requirement of ROW_NUMBER() function to specify any order. As long as you don't need any sorting but just a sequence you can use this. If you want to create a sequence based on any column order remove the "(SELECT 1)" and specify your columns.

Flo

loveleshg Old Hand Points: 384 More actions · Answer 3

Thanks a lot...

So I need to specify the identity column while creating the table?

The sequence returned by this method will always be unique?

loveleshg Old Hand Points: 384 More actions · Answer 4

Thanks .. Florian Reischl

Can we relay on this method in large transaction environment?

any known issues with method of using the ROW_NUMBER?

One doubt if a row gets deleted and new row gets created will it get same ROW_NUMBER?

Paul White SSC Guru Points: 150467 More actions · Answer 5

With your clarified description, the IDENTITY column would be closest in behaviour to an Oracle sequence.

ROW_NUMBER is great for individual set operations, but the next value in the row number 'sequence' is not persisted after the command completes, so it would not meet your needs in this case.

Identity Columns[/url]

Articles tagged 'ROW_NUMBER'[/url]

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi