question

Raj More avatar image
Raj More asked

ETL Strategies: Identity Insert vs. Use Identity Logic

One of my ETL moves about 18 Million rows from one server to another for further processing. I am using the following options `FAST LOAD` For the Identity Column, I have two options: - Use `IDENTITY INSERT` - Don't set any input for the Identity Column, thereby forcing SQL Server to generate a new IDENTITY for each row inserted The value of the Identity column does not matter (it is not used in JOINS) Which option should I choose?
ssissql-server-2008-r2etlidentityidentity-insert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
If you just want to speed up the **process of ETL**, and provided with the identity column values are not used, give a try to omit the IDENTITY column from ETL process and add the IDENTITY column as a separate process later. Cheers.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is very close to what I ended up doing - I just removed the columns.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I would lean towards using identity insert. But as usual, I would try with substantially smaller data sets before finally deciding. If possible, I would also split the data set into smaller chunks to gain performance.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.