November 5, 2012 at 4:17 pm
David F (11/5/2012)
I do appreciate the help...But I 100% disagree that I "should" explicitly define the columns. I understand the performance benefit but I "should" do whatever I need to do to accomplish my task. And my task is to have my local schema AUTOMATICALLY updated based on schema located in an external object. So I need to dynamically check the remote object schema, add the missing fields into my own schema and then write data to the updated schema. So if I explicitly define the columns that means it will require manual intervention to list out the columns OR I'd need to use Dynamic SQL which will not work because of the number of columns, so I'm left with what I'm currently trying to do.
By the book developers will fail, when the solution requires and outside the box solution.
But I appreciate the information, and I will try to figure another way to do what I'm doing. Hopefully I will not end up that it has to be done manually. thank you all again!
I can't believe that you'll have a statement that won't fit in a nvarchar(max). You can disagree with us, but that won't change the way that SQL Server works.
I hope that you can solve this, but I'm afraid that we can't help much (maybe someone can) but it's hard because we don't know everything that's involved in your work environment.
November 5, 2012 at 4:28 pm
I wasn't disagreeing with you about the way that SQL works... I was "hoping" there was a way around it 😉
I'm only disagreeing with the sentiment that listing out the columns is "the way it should be done".
I've got it working now... thanks to the input here. I've moved my record_change_date field to the front and because I control the order I dynamically add columns to the schema, I can ensure that the tables columns stay in the same sequence from here on out. So thank you guys for the input.
I know it's a wierd solution, but basically I'm building a local datawarehouse for SalesForce.com data for our company. Our SalesForce.com schema has objects with over 500 fields. So imagine 500 field * 10 to 15 characters per field. You can see why dynamic SQL wont work. I tried that first and had strings over 13K characters which caused it to fail. Also our company makes schema changes to the SF.com objects weekly, sometimes daily. So it changes alot. I need to pull the data down in it's raw table format and then need to ETL it over into a reporting DB with clearer better formated table names. Sometimes in Salesforce the field name is SR729... this means nothing to anyone, so I need to query the XML, pull in the "label" for SR729 and then craete a reportable field name off the label name because that's how the user base knows the field.
That's the nuts and bolts of what I'm doing, and because the changes are so frequent, it needs to be dynamic. There is a tool out there that does it, but it's over 100K a year. This was the last hurdle before I was able to do the same thing that tool did and I did it in a week and a 1000 dollar component.
Now here is hoping it holds up, because I just dont know what I dont know...
Thanks again for help!
November 6, 2012 at 7:46 am
I am glad you found a way to solve your issue and thanks for letting us know.
I'm only disagreeing with the sentiment that listing out the columns is "the way it should be done".
Using select * is nothing but a PITA. As with anything sql server there are exceptions to that rule. It sounds like this particular situation may be one of those. One of the challenges of using select * for an insert is that if either of the tables change, your code is broken. The debate about using * has been going on for as long as RDBMS systems have been around. Those who don't want to type will insist it is ok, those who have been bitten by it will insist on listing all the columns.
You can see why dynamic SQL wont work. I tried that first and had strings over 13K characters which caused it to fail.
I would disagree that you can't use dynamic sql here. It seems that you are not aware of how long a string can be for dynamic sql. You can use nvarchar(max) which holds 2GB of text data. That is far cry from 13K. You should have no problem using dynamic sql if you use sp_executesql
http://msdn.microsoft.com/en-us/library/ms188001.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 6, 2012 at 9:20 am
Sean, I used varchar(Max) and the string was truncated. Is that a 2005 vs 2008 difference? Because i tried dynamic SQL first. Maybe I'll try it again, as i'd rather list out the columns as that would ensure I dont have later issues.
On a side note, I dont use select *, this is just one of those situations 🙂 But I'm going to go back and retry dynamic SQl but I swear, when i looked at the string it was truncated.
November 6, 2012 at 9:40 am
David F (11/6/2012)
Sean, I used varchar(Max) and the string was truncated. Is that a 2005 vs 2008 difference? Because i tried dynamic SQL first. Maybe I'll try it again, as i'd rather list out the columns as that would ensure I dont have later issues.On a side note, I dont use select *, this is just one of those situations 🙂 But I'm going to go back and retry dynamic SQl but I swear, when i looked at the string it was truncated.
Were you looking at it in SSMS? It will get truncated, it is feature/shortcoming of SSMS. It can be a real nuisance if you aren't familiar with that. You will need to either output the string to file or use a different application to see the entire thing if it gets too long.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply