batchwise Sql script to execute in SSIS

Question

I have SQL script which will generate millions of rows and insert to DB table. How can I execute that script in SSIS in batchwise? Is there any easier solution?

Example:

declare @userinput1 int =1
declare @userinput2 int =10000000;


INSERT INTO table2(col1)
SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n + 10000*laks.n+100000*laks10.n +1000000*crore.n
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) laks(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) laks10(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) crore(n)
WHERE ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n + 10000*laks.n +100000* laks10.n +1000000*crore.n  BETWEEN @userinput1 AND @userinput2
ORDER BY 1

Use a Execute SQL Task? This is very vague here, you need to be more specific on what you're really trying to achieve here. — Thom A
– Thom A ♦, Commented Nov 9, 2018 at 9:55
@lamu Actually Sql script will generate range of sequence number with other column details and insert to table .. — Lajith
– Lajith, Commented Nov 9, 2018 at 10:03
Can you give an example what the sql script you want to execute looks like? — holder
– holder, Commented Nov 9, 2018 at 10:04
actually i want to insert genearted values to table..Sometime i am getting memory exception..so i need to execute this to batchwise — Lajith
– Lajith, Commented Nov 9, 2018 at 10:12

Tab Alleman · Accepted Answer · 2018-11-09 14:15:42Z

1

Use a WHILE loop and only insert a batch at a time, starting from @UserInput1, until you reach @UserInput2.

answered Nov 9, 2018 at 14:15

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

batchwise Sql script to execute in SSIS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related