Updating multiple rows in sql server 2016 parenting single dating internet service
25-Aug-2020 07:12
I then created a procedure that built a loop that parsed the data and inserted it into the target table. We were going to split the staging data into two parts and process each part in parallel.
We had enough server resources for two parallel processes, so we just needed to add two parameters in a procedure to assign the starting row and the ending row of the staging table, and implement parallel calls of procedures in the Informatica workflow.
Before implementing parallel loading we wanted to find a simple way to speed up the performance of our procedure, something small that made a big difference – multiple rows insert.
To compare data-load methods, I chose a common example of a client program loading data. An example of inserting multiple rows: Insert into t2(c1,c2) values(1,’A’),(2,’B’),(3,’C’); which is equivalent to insert into t2(c1,c2) select 1,’A’ union all select 2,’B’ union all select 3,’C’; which was valid in previous versions.
This file had 220,000 rows, each of which had 840 delimited values and it had to be turned into 70 million rows for a target table.
In all, about 184 million rows had to be processed. The ETL project task was to create a payment projection for a period of 70 to 100 years.
How do I pass the parameters to the stored procedure? In this article I’ll describe how I experimented and adjusted my methods to find the best way to insert a million of rows of data into SQL Server.
The more data you’re dealing with, the more important it is to find the quickest way to import large quantities of data.The first prototype powered by Informatica had 840 fixed mappings (Informatica objects).
