30 How to do incremental load in SQL server

[@shaikaniph4125]

Very good video for incremental load using sp. This is exactly how we do in real-time environment.

[@learnssis]

Good to know that you liked the video Shaik.

[@Mahmood-n7s]

Excellent explanation, thanks Aqil sir

[@learnssis]

Thank you Sir.

[@faizanlearning9948]

Very well explained & shown

[@learnssis]

Thank you so much.

[@deepakj6717]

Great efforts i appreciate it.. Thank you Aqil !! hashbytes() function is really useful.. Pls add new concepts like this.

[@learnssis]

Thank you Deepak for appreciating this, sure will do.

[@sureshsugoor]

Nice explanation

[@learnssis]

Thank you Suresh.

[@eltonmelo9728]

Ótimo vídeo, obrigado por compartilhar esse conhecimento!

[@noopurdubey7428]

Thank you very much for the video. Is it possible to run this stored procedure automatically whenever there are changes made to source table ? or may be we have this stored procedure triggered using any Google cloud service like cloud function etc ?

[@learnssis]

Yeah Noopur, you can use trigger to automatically kick of the this stored procedure but I am not sure how much impactful that will be, Sorry I don't have experience with Google cloud.

[@aakashnoopur6382]

Thank you for the response 🙂

[@learnssis]

@@aakashnoopur6382 You are most welcome Aakash.

[@leemabandelu1917]

Also hashbytes can be used for varchaf datatyle, what about int datatype ?

[@learnssis]

We can convert int to varchar before calculating the hash.

[@kaiyao5135]

hi, Thanks for your video! just wonder what if the tables are in different database or different instances?

[@learnssis]

If the tables are in different database then there should not be any problem, you can access a table by giving databasename.schemaname.tablename. However if the tables are in different server then you should try to use SSIS because that works great but still you can create a linked server between 2 instances and can access the table on one server from another server.

[@kaiyao5135]

@@learnssis Thanks!

[@montazeremam]

سلام عالی بود

[@learnssis]

خیلی ممنونم

[@divyasri5383]

Great video 👍.I have a question suppose we have 2 or more sources then how do we need to process the data

[@learnssis]

If you have 2 or more sources, then I would try to combine all sources into one source and then match the source with the destination table.

[@juanodonnell]

why do you compare the hash columns and not compare a simple string concatenation?

[@learnssis]

That's a very great question I think comparing the simple string concatenation can be a good idea. I think hash can be used in the cases where you need to store the value in a table then varbinary(50) can store the hash value easily however if you are going to store the concatenated string then it will take more space and it might take more time to compare the data as the length of string will be more as compare to the hash value.

[@IngenieroAguilera]

Hi, thanks for this video. I have some questions: Why did you use hash bytes function? Was not it enough by using concatenation and comparing? Could this hash bytes function deteriorate query’s performance? Thank you!! 🙌🏻

[@learnssis]

I think you are right. Hashbyte function can be used if we already have a varbinary field in sql table, then we can populate the value to varbinary field using this hash function and while comparing source table with destination table we can compare only on single column that will be varbinary column populated by hash, this way join will be on a single column. But in this example we are calculating the hash on the fly for both source and destination tables and doing a join, so yes you are right calculating the hash is not required here, we can just concatenate and join the fields or we can do a join on all the fields individually like we do in inner join on multiple columns.

[@catulopsae]

Great video. I have an issue when downloading from Snowflake into SQL server some columns are being downloaded with wrong data. What provider should we use to connect in Ssis and sql server?

[@learnssis]

Sorry I have never tried downloading data rom Snowflake, there is no inbuilt component as well in SSIS to download data from snowflake. If somehow you can download data from snowflake to sql server then you can use this method. I think we should be able to download the data from snowflake using C#.

[@catulopsae]

@@learnssis i can share what i know in an email. I will subject it “Snow SSIS”

[@learnssis]

@@catulopsae Thanks for sharing it.

[@natarajbeelagi569]

Thanks!

[@learnssis]

You are most welcome Natraj.

[@leemabandelu1917]

I'm updating only a single column value, in th source table, and executing the. Sp but it shows more than 1 row affected... Why is that ? I wrote stored procedure exactly like yours.

[@learnssis]

Try to run the update query separately and if it still updates more than one record then try to convert the update query with select and see why more than 1 record is getting selected.

[@pratikghimire3938]

This is great video, Please could you also make on SCD Type 2 using SP. I believe, this must be similar process. Thanks a lot

[@learnssis]

Thank you Pratik, sure will try to make a video on SCD Type 2 using SP.

[@mocanada304]

Hello Aqil (The wise! :)) Could you please also add the delete functionality in the solution? Or you can make another video showing that? What if you are transferring a transactional table and the data is deleted in the source but it is still in the destination? It would be nice to have your data fully synchronized with the destination. You are a gem! Can't thank you enough! Thanks again from Canada! P.S. Maybe capture the deleted records information in a log table? So that we can investigate?

[@learnssis]

Okay, I will try to make a video where in addition to insert\delete, we will also take care of delete functionality as well.

[@learnssis]

To handle the deleted records and to delete the records from dest table those are deleted from source you can just add below query to the code in end DELETE FROM Email WHERE NOT EXISTS (SELECT * FROM Staging_Email WHERE Email.Email_Id = Staging_Email.Email_Id) If you want to keep those deleted records in a staging table then first you can keep those records in staging table like below insert into stagingTable SELECT * FROM Email WHERE NOT EXISTS (SELECT * FROM Staging_Email WHERE Email.Email_Id = Staging_Email.Email_Id)

[@angKhoaNguyen20]

Can we update to source table if there are hard deleted events?

[@learnssis]

Sorry I did not get by "hard deleted events". If you want to know the records those got deleted then you can create the trigger on delete and can log that records to a sql table staging and then accordingly you can update your source table from this sql staging table that contains deleted records.

[@angKhoaNguyen20]

@@learnssis thank you. Hard delete in this case means delete completely from database. So I think your idea is good for thid case.

[@b.n.v.sunilkumar5787]

💯

[@learnssis]

Thank you.

[@amlord68]

How does the delete work in this case?

[@learnssis]

This is how the delete will work MERGE INTO [target_table] AS target USING [source_table] AS source ON target.[id] = source.[id] -- When the row exists in both target and source, update the target row WHEN MATCHED THEN UPDATE SET target.[column1] = source.[column1], target.[column2] = source.[column2] -- When the row exists in the source but not in the target, insert the row WHEN NOT MATCHED BY TARGET THEN INSERT ([id], [column1], [column2]) VALUES (source.[id], source.[column1], source.[column2]) -- When the row exists in the target but not in the source, delete it WHEN NOT MATCHED BY SOURCE THEN DELETE;

[@JeetShreeOfficial]

Thanks , Q:- I can delete records using your code also but it is not capturing by Audit/log table , please help me out to track deleted records in Audit table

[@JeetShreeOfficial]

Please ignore , it is fixed . I can get the delete count as well in my audit . Please make more case studies, I like your videos and explanations.

[@learnssis]

@@JeetShreeOfficial Sure, will do.

[@abhilashpatil6778]

Hi, When I run a job from the SQL Server agent I get a step1 error kindly help my source is an Excel file I have created an SSIS package in visual studio if run the package from visual studio it will run successfully but the same package if I run from SQL Server agent, I am getting a step 1 error.

[@learnssis]

Right click on the sql job -> Properties Now Click on Steps -> Edit Click on Execution Options -> use 32 bit run time Check this option (Use 32 bit run time) and save the sql agent job and try again.

[@tvenkateswarlu6712]

HI Aqil , How can we find last updated record in destination table when we dont have any id and Timestamps.

[@learnssis]

I don't think we can in case we don't have Id or timestamp flag.

[@tvenkateswarlu6712]

@@learnssis can we use any triggers ?

[@learnssis]

@@tvenkateswarlu6712 Yes you are correct we can use the triggers in this case. We would need to create 2 types of triggers on destination table, one trigger for insert and another trigger for update and get those records into some staging tables.

[@vinayatavakari6969]

..I need to store output of query in same database is it possible? Data should save in incremental After every run

[@learnssis]

In this demo, both source and destination tables are in the same database. In this video we used 2 tables, our destination table that will be updated\inserted and our source table from which we would need to insert\update data to destination table. You can create a staging table from output of a query and then can compare it with a destination table like how I did in this video.

[@samuna8950]

But sir if source and destination dbs are different then is it possible?

[@chanal4858]

PLS start azure data factory classes

[@mocanada304]

Hello Aqil, Your parents named your correctly. Aqil means knowledgeable. Indeed you are very knowledgeable. Thank you for all these amazing videos. I have 4 questions, if you could answer it, it will greatly appreciated. 1. Is there away you could make the column names get populated dynamically? Like if I want to perform this task for over 100 tables, then it will be too much work to manually type all the columns. 2. Will this work if you have tables that have almost 100 million records? 3. Does this work for records that get deleted in the source? 4. Is there I can find out which records were added or updated? Thank you so much!!!!

[@learnssis]

Ha ha. I am an average person don't have much knowledge. 1. Although I have never tried generating the SP dynamically for different tables, but I think this can be done may be using C# or SQL that it can generate the code for stored procedure based on some tables and their columns. In a configuration table we would need to give the total column names for a table as well as the columns based on which we want to do the join and the columns based on which we want to compare the data. 2. Yes this will work even if you have 100 M records, but you should have proper indexes on the Joining table. If your table has 100 M records, then any other method will also take more time so this will. May be you can try to make another solution in SSIS or in any other language as well and test which works faster, ideally SSIS should perform faster or large data. 3. There is an option in Merge to delete the records from dest if records got deleted from source, but in this video to make the things simple I am only considering insert\update, may be I can make another video to handle the deleted records from source. 4. Yes you can find out but for that may be you would need to add an extra column like lastupdated datetime in the destination table and update the value of lastupdated with currentdate time for any insert\update operation, this way if you get those records from the destination table.

[@yathishkumar9770]

Hi, i would like to know the procedure to load a date column that having a 100 dates with two different formats(7/31/2012, 02-07-2013) in to SQL. i have tried a lot but didn't workout. could you please provide the solution.

[@learnssis]

Convert the data to a specific format explicitly like below select CONVERT(VARCHAR(10), fmdate(), 101)

[@yathishkumar9770]

i have an excel file, in that i have a column with 2 formats of dates and if i tried to load that file to SQL it creating null values instead of dates with this format 7/31/2012

[@learnssis]

@@yathishkumar9770 Try to use a data conversion transformation in between and try to cast the date to DT_DATE. If it does not works then insert the data to an nvarchar column in sql table and update it to a Date column there using the query I sent.

[@yathishkumar9770]

@@learnssis hi, I have tried everything like you suggested but I am unable to load date columns with complete data to SQL from excel with SSIS.

[@learnssis]

@@yathishkumar9770 Sorry to hear this, you can also take at this example, in this video I have shown how to import an excel file into sql server table using SSIS but in script task using C#. kzbin.info/www/bejne/rquVnISmgsSinsk

[@HarishChowdary-y6e]

Hi I need could u to make video on incremental load from one server to another server

[@learnssis]

You got 2 options here 1. You can use the same approach as shown here but you would need to use linked server to access the table from another server. 2. you can use SSIS to do this.

[@ENGCSNISHA]

Hi, can you do the video on mysql code where it can do incremental load from monday to friday.

[@learnssis]

But how it will know which date range you are talking about ?

[@ENGCSNISHA]

@@learnssis We are supposed to take date and find out corresponding day. If it lies from monday to Friday we have to do incremental load or do full load if its saturday Or sunday. This is our problem statement. But I didn't understand how it works

[@learnssis]

@@ENGCSNISHA There is a function DayName() which can tell you the day like below SELECT DAYNAME('2021-03-16') AS 'Result'; So you can create an stored procedure with input parameter as InputDate like I did in one of video of MySQL playlist and then using the function above you can get the dayname, now you can write 2 conditions if dayname lies between 'monday','tuesday','wednesday','thursday','friday' then execute the incremental part code otherwise execute the full load code. I think you can find out how to do incremental load in mysql, I have created a video on how to do it using sql but it can also be done using mysql as well.

[@ENGCSNISHA]

@@learnssis Makes sense. I ll try this. Thank you so much.

[@blackisblack22]

It won’t work because of nulls.

[@learnssis]

You would need to take care of NULLs using IsNULL function.

[@leemabandelu1917]

I'm joining on Primary key only.