Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2011-10-18 : 16:24:39
|
| Having some odd results and not knowing why. I have query to insert some data into one particular table. This query spans 97,000 lines fo approximately 24,000 rows of inserts. Even if i leave off the command SET NOCOUNT=ON at the beginning of the query, it will, more quickly than expected, return the result "Query completed with errors", but the messages window will be blank. No errors and no results show in the messages window. When looking at the contents of that table of this result, no data has been inserted. Now, if i take that same query and select, say, the first 500 inserts and only execute those, they all insert as expected and the data shows up in the table. Whether or not i can find out "why" this is happening, is there a way to generate a GO command after a set number of inserts in the query? Say like, every 500th INSERT is followed by a GO? |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-10-18 : 17:20:34
|
| Ugh. As an extension of the above question, as this issue relates to the same table:I'm working on converting and importing a customer's data from their excel sheets. This data is a collection of legal information which includes addresses and such in a number of columns per row. It is very poorly organized, as you might expect. In most cases, an individual's address and explicit directions to that address are broken up amongst several columns. The character lengths in their columns far exceed ours in our db. So i'm left with trying to map their fields to ours as best is possible. However, the issue of our character string lengths is not something i can get around. Is there any way to get SQL to automatically truncate the character strings in columns instead of just whining to me that it could not/would not do the insert because "the data would be truncated." Is there any override for this? I've googled the snot out of it and am coming up with nothing.thanks |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-19 : 05:09:14
|
| no way to do that on the fly. however, one work around is to declare fialds with sufficinetly large lengths and after population check max length of data present and changing lengths accordingly.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-10-19 : 11:58:09
|
| How about this then...? Coding the insert to first check the string against the string length constraint for the column it's being pushed to. If the data is in jeopardy of being truncated due to length, then insert that data to a different specified column? |
|
|
|
sqlmaster555112
Starting Member
13 Posts |
Posted - 2011-10-19 : 12:00:36
|
| Try this on the first question u postedDECLARE @Counter int;SET @Counter = 1;WHILE @Counter < 10BEGIN SELECT @Counter SET @Counter = @Counter + 1 IF @Counter = 4 GOTO Branch_One --Jumps to the first branch. IF @Counter = 5 GOTO Branch_Two --This will never execute.ENDBranch_One: SELECT 'Jumping To Branch One.' GOTO Branch_Three; --This will prevent Branch_Two from executing.Branch_Two: SELECT 'Jumping To Branch Two.'Branch_Three: SELECT 'Jumping To Branch Three.'@sqlmasterhttp://www.intelcs.com/SQL_Server_Consultant.aspx |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-10-19 : 12:13:02
|
| Thanks. i will use that in the future, but i found the error that was overloading the messages pane in mgmt studio. I was using SqlAssistant to format the code after RazorSql pulled the data over from the excel sheet for the import. When SqlAssitant did the reformatting of the data, it was dropping a comma after a large number of inserts, thereby creating a huge number of syntax errors. With this error occurring in over 825,000 lines, mgmt studios ability to report those errors basically overloaded. I'm still struggling with this ridiculous truncation issue on the same table, however. |
|
|
|
|
|
|
|
|
Management Studio, INSERT statements