How would I load 100+ million rows into sql server table with additional column with range of values from 1 to 1000

Question

Hi,

I need to load table as a result of join of other tables, but this result (100+ million rows) needs additional column with range of values from 1 to 1000. I cannot use identity column obviously, and I don't want to use while loop to load in batches.

Any idea?

Thanks

Answer 1

don't want to use while loop to load in batches.

Hm, with that many rows, you are probably better off inserting the rows in batches. It could be more than a mouthful to try to insert all at once.

The extra column can easily be achieved with

row_number() (ORDER BY (SELECT 1)) % 1000 + 1 AS extra_column

Here I've said that I don't care about the order of the numbering. If you want the numbering be based some order of the data, you are in for quite a challenge.

Answer 2

Just to add, adding column as:

INSERT <TargetTable>

SELECT *, NTILE(1000) OVER(ORDER BY <columnname> ASC) AS Ind

FROM <SourceTable>

...takes forever (20+ mins), which is not acceptable in this scenario.

Thanks

Answer 3

Hi @Ed

I need to load table as a result of join of other tables

Try this: insert the join result into target_table with additional identity column IDENTITY(1,1).

Then calculate the Ind with the identity column like this:

SELECT *,FLOOR((Identity_id-1)/(SELECT COUNT(*)/1000.0 FROM TargetTable))+1 AS Ind
FROM TargetTable 

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.