| Author |
Topic |
|
chikku6
Starting Member
9 Posts |
 Posted - 2014-06-17 : 09:29:48
|
| Hi,Currently we have a scenario where we handle data using a temporary table and then transfer it back to the original data table.Lets Just say Table A has the original Data.We now store Data in a temporary table.The data is bulk inserted into the temporary table and once this process is done, we then do a merge of the temporary and the Original Table A using the merge statement which updates when there is data already present for old key else inserts the new data?IS there a better way or what is the best way to handle the above scenario? |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-06-17 : 10:53:57
|
| Why don't you just merge the new data into the original table? |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-06-18 : 01:28:13
|
| What is the source of the new data?Is it coming from use input\excel\text file ? How much data?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-06-18 : 01:28:13
|
| What is the source of the new data?Is it coming from use input\excel\text file ? How much data?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-18 : 03:10:53
|
Use TABLOCK query hint.Turn on traceflag 610.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
abacusdotcom
Posting Yak Master
133 Posts |
Posted - 2014-06-18 : 15:20:42
|
quote:
Originally posted by SwePeso
Use TABLOCK query hint.Turn on traceflag 610.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Hi Boss, am just thinking aloud.. does ssis uses these settings?I sign for fame not for shame but all the same, I sign my name. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-06-19 : 00:43:31
|
| You include the TABLOCK as part of the sql statementJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
chikku6
Starting Member
9 Posts |
Posted - 2014-06-19 : 04:15:56
|
| The source for the new table is the new temporary table which we use to merge with the original data table using the merge statement.We are not merging the data directly because it would mean running thousands of individual sql statements. instead of one on the whole table ? i think this is more inefficient . Is it not ? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-19 : 06:13:16
|
You can use snapshot isolation mode and it will not matter.Then you are working with a temporary copy anyway and the "redo" phase is only a "COMMIT TRAN".
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
chikku6
Starting Member
9 Posts |
Posted - 2014-06-19 : 06:41:12
|
| TABLOCK did not give any performence improvement at all. we run these SQL statements from a c# program and do not use SSIS or access SQL server and manipulate it directly :).We first insert fresh data into a temp table using bulk insert. then we update our main table from the temporary table using the merge statement.Please do help :) |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-06-19 : 08:36:27
|
| Why don't you just merge the new data into the original table? |
|
|
|
chikku6
Starting Member
9 Posts |
Posted - 2014-06-19 : 08:49:56
|
| @gbritton merging the new data with the original data means that we would essentially run a tsql command command for every row which I Think very inefficient rather than running a SQL command on a bunch of rows :-) |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-19 : 11:05:32
|
quote:
Originally posted by chikku6
@gbritton merging the new data with the original data means that we would essentially run a tsql command command for every row which I Think very inefficient rather than running a SQL command on a bunch of rows :-)
He is talking about the MERGE statement which is set operation.http://msdn.microsoft.com/en-us/library/bb510625.aspx |
|
|
|
chikku6
Starting Member
9 Posts |
Posted - 2014-06-26 : 09:59:54
|
| Any suggestions for this??? |
|
|
|
|
Faster way to transfer data between tables.