Graph DB edge tables

  • I'm one of those people who insist on an INSERT statement showing an explicit list of column names to be inserted. However, with the EDGE tables in SQL 2017 that seems to be impossible since the $from_id and $to_id columns have an arbitrary suffix appended to them, which leads me to believe that only dynamic SQL could make me feel "happy". Or is there some other way to accomplish that, like in some reserved words that identify those columns without specifically naming them? Also, how would I "re-parent" or "re-sibling" a row in an UPDATE statement if I don't know the column names? Just delete and re-insert?

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • Well, there's always INFORMATION_SCHEMA.COLUMNS to play with.   If you go the dynamic SQL route, you can find all the column names for any given table, and then use the QUOTENAME function to enclose them in brackets so you don't have column name issues.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson - Wednesday, December 20, 2017 12:49 PM

    Well, there's always INFORMATION_SCHEMA.COLUMNS to play with.   If you go the dynamic SQL route, you can find all the column names for any given table, and then use the QUOTENAME function to enclose them in brackets so you don't have column name issues.

    I'm aware of that, Steve, but that's exactly what I'm trying to avoid.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

  • sgmunson - Wednesday, December 20, 2017 12:49 PM

    Well, there's always INFORMATION_SCHEMA.COLUMNS to play with.   If you go the dynamic SQL route, you can find all the column names for any given table, and then use the QUOTENAME function to enclose them in brackets so you don't have column name issues.

    Personally, I stay away from INFORMATION_SCHEMA views.  If I need metadata information I go to the sys.<system views> since they have more information should I need it.

  • Jan Van der Eecken - Wednesday, December 20, 2017 1:12 PM

    sgmunson - Wednesday, December 20, 2017 12:49 PM

    Well, there's always INFORMATION_SCHEMA.COLUMNS to play with.   If you go the dynamic SQL route, you can find all the column names for any given table, and then use the QUOTENAME function to enclose them in brackets so you don't have column name issues.

    I'm aware of that, Steve, but that's exactly what I'm trying to avoid.

    Yeah, I understand, ... but ...  what's the alternative?   If you have specific columns that you know the starting x number of characters for the column name, dynamic SQL is probably the ONLY way to have automation handle things.   Also, you might be able to use a Script Task within SSIS and use ADO code with VB or C# to maybe make the code more specific than doing it all in T-SQL, but no matter how you slice it, something will have to be dynamic.

    I've seen Lynn's post about using the sys.tables or sys.views or sys.columns type of metadata info instead of INFORMATION_SCHEMA, and for some things, those are more practical, but if you just need to identify column names for some dynamic SQL ...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson - Thursday, December 21, 2017 7:01 AM

    Jan Van der Eecken - Wednesday, December 20, 2017 1:12 PM

    sgmunson - Wednesday, December 20, 2017 12:49 PM

    Well, there's always INFORMATION_SCHEMA.COLUMNS to play with.   If you go the dynamic SQL route, you can find all the column names for any given table, and then use the QUOTENAME function to enclose them in brackets so you don't have column name issues.

    I'm aware of that, Steve, but that's exactly what I'm trying to avoid.

    Yeah, I understand, ... but ...  what's the alternative?   If you have specific columns that you know the starting x number of characters for the column name, dynamic SQL is probably the ONLY way to have automation handle things.   Also, you might be able to use a Script Task within SSIS and use ADO code with VB or C# to maybe make the code more specific than doing it all in T-SQL, but no matter how you slice it, something will have to be dynamic.

    I've seen Lynn's post about using the sys.tables or sys.views or sys.columns type of metadata info instead of INFORMATION_SCHEMA, and for some things, those are more practical, but if you just need to identify column names for some dynamic SQL ...

    Just my preference, especially since INFORMATION_SCHEMA views aren't as standard as some think.

  • I'll admit that it's a totally different take on the answer to this but, like many things, I find that the built in functionality is just totally bloated and would probably never use it.  I'll write my own, instead.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Thursday, December 21, 2017 9:24 AM

    I'll admit that it's a totally different take on the answer to this but, like many things, I find that the built in functionality is just totally bloated and would probably never use it.  I'll write my own, instead.

    If I am reading this right, it makes sense considering what I use from the system tables on a regular basis it would be reasonable to create views or functions to eliminate the redundant code I seem to write.

  • Lynn Pettis - Thursday, December 21, 2017 9:41 AM

    Jeff Moden - Thursday, December 21, 2017 9:24 AM

    I'll admit that it's a totally different take on the answer to this but, like many things, I find that the built in functionality is just totally bloated and would probably never use it.  I'll write my own, instead.

    If I am reading this right, it makes sense considering what I use from the system tables on a regular basis it would be reasonable to create views or functions to eliminate the redundant code I seem to write.

    My bad, Lynn.... I was talking about graph tables and not things like sys.columns, etc.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • My question remains, why did MS decide to add this useless suffix to the @from_id and @to_id columns? It just makes a joke of the "Explicitly name columns" best practice that we have been taught for ages.

    Edit: fixed a typo.

    --------------------------------------------------------------------------
    A little knowledge is a dangerous thing (Alexander Pope)
    In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply