2
SQL Server 2012 (SP1) with 64GB ram using 50GB for max server memory
sp_configure 'locks' = 0
trace flags 1211 & 1224 are off
using READ_COMMITTED
SET LOCK_TIMEOUT 0

While using MERGE I get this error:

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

Too many developers are experiencing ongoing problems with merge, so how else can we efficiently do cascading copy of rows and related rows for parent/child tables. E.g. tableA has many tableB rows and tableC has many tableD rows etc...

MERGE is useful when creating the new identity values, while on the same row keeping the old identity to look back the data for the next table insert, is there alternatives?

Using the single table insert OUTPUT command, we cannot get the old and new ID at the same time

INSERT INTO tableA
OUTPUT  inserted.[ID],inserted.[col1],inserted.[col2],inserted.col3,
INTO #tmptable

What are the best options available to do this in SQL Server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Skeezwiz
  • 31
  • 7
  • can a serialized isolation level help? – Recursive Oct 08 '14 at 13:29
  • Already using READ COMMITTED and not helping, searching the net there are too many problems using MERGE, and using transactions gets even more uglier, resolved to using a neat hack that I found: tableA create a sourceID field, then do:INSERT INTO tableA(col1,col2,col3,sourceID) OUTPUT inserted.[col1],inserted.[col2],inserted.col3,inserted.[currentID] INTO #tmptable SELECT col1,col2,col3,currrentID as sourceID. This way you can get the new and old ID on the same row after INSERT finishes, hopefully this helps others out there. – Skeezwiz Oct 10 '14 at 05:10
  • using query hints e.g. forceseek and filtered cte or view targets for the MERGE didn't help either - http://stackoverflow.com/questions/7407560/t-sql-merge-performance-in-typical-publishing-context?rq=1 – Skeezwiz Oct 10 '14 at 06:04
  • I'd have no idea how to write this without sitting down for a bit and swinging at the problem. You may have more skill than I. What comes to mind for me would be some sort of recursive cte solution where you use the @@SCOPE_IDENTITY or proper flavor of to seed the recursion. And though I cringe to suggest, is a cursor an option for you? That I could see working nicely. – Matt Jan 30 '15 at 21:44
  • Could you clarify what you mean by "old ID"? I don't understand how the insert statement would have an "old ID"? – Sako73 Jul 14 '15 at 14:32
  • I had to revisit this code quite recently to make changes and what I did was: `code` (1) create table #tmptable ([oldID] [bigint] ,[newID] [bigint] ), (2) add the oldID permanent column to the main table, which is exactly the same as the PK for that row (3) INSERT INTO [dbo].[main table] OUTPUT inserted.[oldID],inserted.[newID] INTO #tmptable SELECT oldID , column1, column2 ... `code` - this is the only way to get the data inserted for new ID and old ID to come up on same row, i'm surprised msft doesn't have this command in sql yet as merge is terribly slow! – Skeezwiz Jul 23 '15 at 02:43

1 Answers1

0

I had to revisit this code quite recently to make changes and what I did was: code (1) create table #tmptable ([oldID] [bigint] ,[newID] [bigint] ), (2) add the oldID permanent column to the main table, which is exactly the same as the PK for that row (3) INSERT INTO [dbo].[main table] OUTPUT inserted.[oldID],inserted.[newID] INTO #tmptable SELECT oldID , column1, column2 ... code - this is the only way to get the data inserted for new ID and old ID to come up on same row, i'm surprised msft doesn't have this command in sql yet as merge is terribly slow!

Skeezwiz
  • 31
  • 7