December 18, 2017 at 1:48 pm
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?
December 20, 2017 at 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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 20, 2017 at 1:12 pm
sgmunson - Wednesday, December 20, 2017 12:49 PMWell, 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.
December 20, 2017 at 1:39 pm
sgmunson - Wednesday, December 20, 2017 12:49 PMWell, 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.
December 21, 2017 at 7:01 am
Jan Van der Eecken - Wednesday, December 20, 2017 1:12 PMsgmunson - Wednesday, December 20, 2017 12:49 PMWell, 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)
December 21, 2017 at 9:12 am
sgmunson - Thursday, December 21, 2017 7:01 AMJan Van der Eecken - Wednesday, December 20, 2017 1:12 PMsgmunson - Wednesday, December 20, 2017 12:49 PMWell, 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.
December 21, 2017 at 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2017 at 9:41 am
Jeff Moden - Thursday, December 21, 2017 9:24 AMI'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.
December 21, 2017 at 3:51 pm
Lynn Pettis - Thursday, December 21, 2017 9:41 AMJeff Moden - Thursday, December 21, 2017 9:24 AMI'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
Change is inevitable... Change for the better is not.
December 21, 2017 at 3:53 pm
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply