30 How to update a large SQL table in batches | How to update a large SQL table in Chunks

How to update a large SQL table in batches
Download the file\script used in the Video from below link
drive.google.c...
SSIS Tutorials: • SSIS Tutorials
SSIS real time scenarios examples: • SSIS real time scenari...
SSIS Interview questions and answers: • SSIS Interview questio...
How to update a large SQL table in batches
Can we do bulk UPDATE in SQL?
How can I UPDATE more than 1000 records in SQL?
How do I UPDATE a large table?
How can I UPDATE millions of rows in SQL?
Happy Learning.
If you have any questions or suggestions please comment on the video or write to me at “[e-mail]”

[@rprasad8271]

Hi Aqil, Thanks for ur effort. One request, plz do a video to load data in batch wise from one server to another server in SSIS. I did in my project this with pure T - Sql. Not sure how to do it in SSIS. Pla consider my request.

[@learnssis]

Sure Prasad, this is in my to do list, I will surely make a video on this one.

[@PAULA-bi6fy]

Hello Aqil, thank you for your material. I would like to request a video about how SSIS properties influece the performance of a SSIS package. I created a project using SSIS and when I change batch size and lobchunksize properties, the project works, if I don't change them the project doesn't run. I would appreciate your help!! Thank you!

[@learnssis]

Okay, I will try to make a video on this in future.

[@NaviK-u2l]

Hi Aqil. I have a question will this query work in Oracle sql developer? Using liquibase scripts. Pls help me. I would like to update in same table from one column to another column.

[@learnssis]

The syntax will be a bit different. however you can take help from chatgpt to convert the sql script from sql server to Oracle.

[@mrnimbus2556]

Hello! Awesome tutorial! Can you make a tutorial on how to update table with multiple columns base on ID? Like 3 or more columns need to batch update. Thanks!

[@learnssis]

In the update statement, you can put multiple columns separated by comma, every thing else will be same, you just need to add more columns to update query a.firstcolumn = b.firstcolumn, a.secondcolumn = b.seccondcolumn, a.thirdcolumn = b.thirdcolumn It is very straightforward, let me know if you face any issue updating more columns, it should be same like updating a single column, we can just append more columns like above to the update query. In below video I updated 2 columns in a table kzbin.info/www/bejne/opeZZoOepqpgrM0

[@sanjuladissanayake5295]

Hi Sir, How can we delete the deleted records in the source system using this method? Your help is highly appreciated!

[@learnssis]

First you need to figure out what records are deleted in source system And then the delete query can be executed in chunks. Below is the video on how to figure out which records are deleted in source system kzbin.info/www/bejne/mIvEmq2Bq7d0btE Once you have the deleted records in a staging table, you can delete them from your destination table in chunks. DECLARE @BatchSize INT = 10000; -- Define the batch size WHILE (1=1) BEGIN -- Delete the top 10,000 rows that match the join condition DELETE TOP (@BatchSize) A FROM DestinationTable AS A JOIN StagingTable AS B ON A.SomeColumn = B.SomeColumn -- Exit the loop if fewer rows were deleted than the batch size IF @@ROWCOUNT < @BatchSize BREAK; END;

[@sanjuladissanayake5295]

@@learnssis Thank you so much, Sir♥🙏

[@somidixit9673]

Can you please tell if there is a way to update columns in one query by joining on two tables? Let’s say I have 6 columns and 3 columns need to be updated from one table and another 3 by joining on another table and each join uses different field?

[@learnssis]

Yeah you can simply use sql join and it should work, like below update a set a.column1 = b.column1, a.column2 = b.column2, a.column3 = b.column3, a.column4 = c.column4, a.column5 = c.column5, a.column6 = c.column6 from table1 a inner join table2 b on a.id = b.id inner join table3 c on a.id = c.id

[@somidixit9673]

Thanks, I used this query to update a table that has 600 million records in batches of 100 thousand. Without stopping the query I checked the log table and all the columns in the log table are showing values as expected but the count column has 0 value for each row. Does this mean that none of the columns are getting updated while the query is still running? I thought the purpose of this query is it will keep updating the table in smaller chunks. The query errored out because size of temp db was full and none of the columns were updated.

[@learnssis]

@@somidixit9673 If the count column has a value as 0 it means none of the record is being updated, you can try the query first on a small table and test it and make sure it is updating the data, I shown in this video updating a table and usually I use this query to update the records, may be the min id or max id are not set properly or where clause of the update query has some issue. Double check those things and see how I am doing this in video. You can download the table to update from this video and try testing that first so that you know what we are doing and how we are doing.

[@somidixit9673]

I ended up using a cursor on one of the field in the table to create smaller chunks. Thanks for the video and your feedback.

[@darshanapatil2920]

video on SSRS SSAS also

[@learnssis]

Actually after creating few more videos on SQL, I am planning to make videos on either on SSRS or on Power BI.

[@lezyrabin3474]

Hi Aqil can you please make video on records i insert in batches from flat file to oledb destination

[@learnssis]

if you are reading data from flat file to sql server, then it will always be faster to load all data once instead of reading it in chunks. If you will try to read it in chunks then it will take a lot of time to read it. If you have some limitations writing all data once to destination table, then try to insert all data to a staging table first and then from staging table insert to the main table in chunks.

[@bazyleus456]

Hi, Aqil. I would like to see it in ssis package as well. Regards :)

[@learnssis]

Yeah you can put the whole query inside the execute sql task in SSIS package.

[@darbardarbar9460]

What are important best practices for using SSIS

[@learnssis]

Yeah I need to make video on this topic. Its in my list and will work on it.

[@darbardarbar9460]

@@learnssis HI sir share link in this topic

[@learnssis]

@@darbardarbar9460 kzbin.info/www/bejne/aaHElKuNmrmWm5Y kzbin.info/www/bejne/baSudpqjZ9qEe68

[@roh322]

Hello Sir, How can we quickly update table with composite index?

[@learnssis]

If the join will be on 2 columns then of course it will take more time as compare to joining tables on a single column and having clustered index on it, I don't have any solution here.

[@sufiya2024]

Can you tell how to insert in chunks

[@learnssis]

That's a great question and I think I should have created a video on this topic as well. Actually there are multiple scenarios of inserting data into chunks. For example if you have a large sql table and you want to export the data to multiple CSV files in chunks or you just want to move data from one sql table to another sql table in chunks then there are few options here. 1. Either you can find a column in source table which can actually divide the whole data into some parts, for example if you have US data and there is a State field there, and there are 51 states in US, thus you can make 51 chunks of this table and using foreach loop container with ado enumerator you can insert data to another table in 51 chunks and pass the State name to the sql query. 2. Another option is that you can select the Id column from your source table and use Ntile sql function and pass the value of ntile how many chunks you want to create for example 100 and insert the data to a temp table, the table will contain Id and Chunk. Now in the for loop container the loop will run from 1 to max chunk and while fetching data from source query you will make a join between temp table to your source table based on id where temp.chunk=1 and next time it will be chunk=2 and so on. 3. There can be some other options as well where in a staging table based on the total id from sql table we can divide the id to multiple parts like part1 will contain id from 1 to 10000 and part2 will contain id from 100001 to 20000 and so on and then in foreach loop we will select the start and endid from this table and will pass it to source sql query.

[@shubhangilamkhade4681]

Can we update a single table in same way

[@learnssis]

Yes, we can and I always do that.

[@maf6085]

How to update in batches through Rest Api?

[@learnssis]

You can put this code inside an sp and then call the sp from rest api.

[@darbardarbar9460]

how to upload JSON file without using script component task

[@learnssis]

If you want to load a JSON file without using script component then you can download the third party components zappysys.com/products/ssis-powerpack/ssis-json-file-source/?gclid=Cj0KCQjwyOuYBhCGARIsAIdGQRMuNTj9qq04GsSFOB_EpUcVm4MR1MC3KGh2byoLN4ryMqfoJ7lXGIcaAoCxEALw_wcB www.cdata.com/kb/tech/json-ssis-task-import-2008.rst But for above third party component, you would need to purchase a license from the same company.

[@kumarg2580]

why did you add 100000 in max(Id)?

[@learnssis]

Just to be on safe side that loop will run 1 extra time and will update any remaining records. Actually I have noticed if we won't add it then last few records can be missed to update, that's why I add it.

[@kumarg2580]

@@learnssis Thank you!!

[@shalu8644]

Can you please write comment for each line ??

[@learnssis]

Okay, I will write when I will get a chance today.