Hello Everyone,
Here I need suggestions while I am trying to do some transaction in sql.
I am using "Microsoft SQL Azure (RTM) - 12.0.2000.8 Oct 1 2020 18:48:35". I am getting data from On-premises data-sources using Azure Logic App (recurrence).
The logic app insert the data in my some of tables lets suppose tableHR, tableProjects. Before it insert the data it truncate the both tables, so that I can get the updated data every time recurrence happens.
Now I am doing some manipulation using both of tables and inserting the manipulates data into my some other tables (which I will use late for my application).
Table tableHR having prox 10000 of records and tableProject is having aprox. 6000 records.
So here I need that whatever the manipulated data I am inserting into my other tables if it is already there then it should update the records else if records does not exists ten it should insert.
I can write the procedure for that, but this data should be very correct to run my application. So what point I need to consider (concurrency & performance) while doing this transaction so that every time I call this procedure it should run and update my existing tables correctly.
Please guide.
November 25, 2020 at 7:16 pm
If I understand this, you are inserting data into a staging table in Azure SQL from an on-premises table. Then, you want to essentially upsert or merge this data into another table in Azure SQL?
There is a MERGE command, but it's not very efficient. I assume you have some PK type value in the final table. Is there some matching PK value in the staging table?
Usually we update the final from staging, by joining on the PK. Often, I then would run a delete on the staging table with the same join. This leaves me only insert data to move over.
Indexing matters, so if you can index the PK in both tables, this will help.
November 27, 2020 at 8:18 am
Steve Jones - SSC Editor wrote:If I understand this, you are inserting data into a staging table in Azure SQL from an on-premises table. Then, you want to essentially upsert or merge this data into another table in Azure SQL?
Yes, you got it right!
Steve Jones - SSC Editor wrote:There is a MERGE command, but it's not very efficient.
This is not a merge command. I am just using the below query method
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1yes, I also need to put a condition here in the above query, that if records already exists then those should be updated and if don't exists then those should be inserted.
Thank You
this is a merge process. you are merging data in two tables.
As I wrote. Update first, then insert. If you want to INSERT only, you need a WHERE condition that looks for rows that are not matched, which is an inefficient and slow process. The way to do this process is:
I cannot give you a query, because I don't know the columns or the structure of the data. You need a column or combination of columns that uniquely identify the rows.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply