MERGE INTO insertion order

By : spender

I have a statement that looks something like this:

MERGE INTO someTable st
    SELECT id,field1,field2,etc FROM otherTable
) ot on st.field1=ot.field1
    INSERT (field1,field2,etc)
    VALUES (ot.field1,ot.field2,ot.etc)

where otherTable has an autoincrementing id field.

I would like the insertion into someTable to be in the same order as the id field of otherTable, such that the order of ids is preserved when the non-matching fields are inserted.

A quick look at the docs would appear to suggest that there is no feature to support this.

Is this possible, or is there another way to do the insertion that would fulfil my requirements?

EDIT: One approach to this would be to add an additional field to someTable that captures the ordering. I'd rather not do this if possible.

... upon reflection the approach above seems like the way to go.

By : spender


Following up on MikeTeeVee's answer.

Using TOP will allow you to Order By within a sub-query, however instead of TOP 9223372036854775807, I would go with


Unlikely to reach that number, but this way just makes more sense and looks cleaner.

By : MMartin

I cannot speak to what the Questioner is asking for here because it doesn't make any sense.

So let's assume a different problem:

Let's say, instead, that I have a Heap-Table with no Identity-Field, but it does have a "Visited" Date field.
The Heap-Table logs Person WebPage Visits and I'm loading it into my Data Warehouse.
In this Data Warehouse I'd like to use the Surrogate-Key "WebHitID" to reference these relationships.
Let's use Merge to do the initial load of the table, then continue calling it to keep the tables in sync.

I know that if I'm inserting records into an table, then I'd prefer the ID's (that are being generated by an Identify-Field) to be sequential based on whatever Order-By I choose (let's say the "Visited" Date).
It is not uncommon to expect an Integer-ID to correlate to when it was created relative to the rest of the records in the table.
I know this is not always 100% the case, but humor me for a moment.

This is possible with Merge.

Using (what feels like a hack) TOP will allow for Sorting in our Insert:

MERGE DW.dbo.WebHit AS Target --This table as an Identity Field called WebHitID.
    SELECT TOP 9223372036854775807 --Biggest BigInt (to be safe).
           PWV.PersonID, PWV.WebPageID, PWV.Visited
      FROM ProdDB.dbo.Person_WebPage_Visit AS PWV
     ORDER BY PWV.Visited --Works only with TOP when inside a MERGE statement.
) AS Source
  ON Source.PersonID  = Target.PersonID
 AND Source.WebPageID = Target.WebPageID
 AND Source.Visited   = Target.Visited
WHEN NOT MATCHED BY Target THEN --Not in Target-Table, but in Source-Table.
    INSERT (PersonID, WebPageID, Visited) --This Insert populates our WebHitID.
    VALUES (Source.PersonID, Source.WebPageID, Source.Visited)
WHEN NOT MATCHED BY Source THEN --In Target-Table, but not in Source-Table.
    DELETE --In case our WebHit log in Prod is archived/trimmed to save space.

You can see I opted to use TOP 9223372036854775807 (the biggest Integer there is) to pull everything.
If you have the resources to merge more than that, then you should be chunking it out.
While this screams "hacky workaround" to me, it should get you where you need to go.

I have tested this on a small sample set and verified it works. I have not studied the performance impact of it on larger complex sets of data though, so YMMV with and without the TOP.

There is no portable bytecode specification for Ruby, and thus also no standard way to load precompiled bytecode archives. However, almost all Ruby implementations use some kind of bytecode or intcode format, and several of them can dump and reload bytecode archives.

YARV always compiles to bytecode before executing the code, however that is usually only done in memory. There are ways to dump out the bytecode to disk. At the moment, there is no way to read it back in, however. This will change in the future: work is underway on a bytecode verifier for YARV, and once that is done, bytecode can safely be loaded into the VM, without fear of corruption. Also, the JRuby developers have indicated that they are willing to implement a YARV VM emulator inside JRuby, once the YARV bytecode format and verifier are stabilized, so that you could load YARV bytecode into JRuby. (Note that this version is obsolete.)

Rubinius also always compiles to bytecode, and it has a format for compiled files (.rbc files, analogous to JVM .class files) and there is talk about a bytecode archive format (.rba files, analogous to JVM .jar files). There is a chance that Rubinius might implement a YARV emulator, if deploying apps as YARV bytecode ever becomes popular. Also, the JRuby developers have indicated that they are willing to implement a Rubinius bytecode emulator inside JRuby, if Rubinius bytecode becomes a popular way of deploying Ruby apps. (Note that this version is obsolete.)

XRuby is a pure compiler, it compiles Ruby sourcecode straight to JVM bytecode (.class files). You can deploy these .class files just like any other Java application.

JRuby started out as an interpreter, but it has both a JIT compiler and an AOT compiler (jrubyc) that can compile Ruby sourcecode to JVM bytecode (.class files). Also, work is underway to create a new compiler that can compile (type-annotated) Ruby code to JVM bytecode that actually looks like a Java class and can be used from Java code without barriers.

Ruby.NET is a pure compiler that compiles Ruby sourcecode to CIL bytecode (PE .dll or .exe files). You can deploy these just like any other CLI application.

IronRuby also compiles to CIL bytecode, but typically does this in-memory. However, you can pass commandline switches to it, so it dumps the .dll and .exe files out to disk. Once you have those, they can be deployed normally.

BlueRuby automatically pre-parses Ruby sourcecode into BRIL (BlueRuby Intermediate Language), which is basically a serialized parsetree. (See Blue Ruby - A Ruby VM in SAP ABAP(PDF) for details.)

I think (but I am definitely not sure) that there is a way to get Cardinal to dump out Parrot bytecode archives. (Actually, Cardinal only compiles to PAST, and then Parrot takes over, so it would be Parrot's job to dump and load bytecode archives.)

This video can help you solving your question :)
By: admin