0

I've a DE which has 2M records and would like to split it to 1.1M and 0.9M using SQL query. A query like below throws error. Any thoughts?

select 
A, B, C, ROW_NUMBER() OVER (ORDER BY _CustomObjectKey) as rowNum
from 
[TEST_TABLE]
where
rowNum < 1100000


select 
A, B, C, ROW_NUMBER() OVER (ORDER BY _CustomObjectKey) as rowNum
from 
[TEST_TABLE]
where
rowNum >= 1100000
2

First, You need to split this into two seperate SQL Queries. Afterwords, you have multiple ways to split your data.

Not knowing the attributes of your data extensions, this would be a generic solution to what you're trying to do :

1st Query : (Let's say this goes into a Data Extension named [Target_DE_1] for the 0.9M records)

Select Top 45 Percent
A, B, C
from 
[TEST_TABLE]
Order by [SomeKindOfAttribute] desc/asc

2nd Query : Where your 1.1M records will go

Select 
A, B, C
from [TEST_TABLE] As A
Left Join [Target_DE_1] As B On A.Key = B.Key
Where B.Key Is Null

(*) SomeKindOfAttribute would be an orderable attribute in your DE

(**) Key being a unique ID in your DE

| improve this answer | |
0

Why use row numbers on _customobjectkey, when it already increments by one for each row? I’d suggest finding the min and max values for _customobjectkey and using that in your WHERE.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.