| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2011-10-24 : 11:27:26
|
| May not be what i'm actually looking for, but here his the scenario.In wrting for a long series of inserts for converting a customers data over to SQL (from excel tables), i have a limited number of places in which i can put there data on the sql side. Our sql fields are constrained differently than the customers original data. For instance, our "Check No" column in SQL is a Varchar(10) and many of their "check no" fields hold data that is 20 to 25 characters in length. I have a "junk drawer" column on our end that i can deposit some of the data from the customer's end that does not fit in the cell it should due to the constraints on our end. I have case expressions for one particular column that deposits data from that column into the "Comments" field with a concatenated explanation as to what that data is. However, i have other columns now that have data that does not fit in their intended columns as well. I would "like" to write CE's to evaluate that data and place it in the "Comments" column as well if it is beyond the original columnns character length constraints. The question is, is it possible to direct data from two diffent streams into the same column and have it "append" itself to the data that is already there?? For instance, the "Case No." column looks at the data being sent to it and if it is longer than 20 characters, it sends that data to the "Comments" column with a hard coded prefix of "The case no for this entry is .....". I would like the column for "Check no." to make the same evaluation and send it's data there as well with the hardcoded prefix of "The check numbers used in this case are:.....". Therefore, in the comments column for that particular row, if both sets of data are forwarded there would look like "The case no for this entry is....., and the check numbers used in this case are:....."Is there any coding that can be used to accomplish this?thanksJames |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-24 : 11:36:24
|
you can use conditional update statement like belowUPDATE tSET CaseNo=CASE WHEN LEN(CaseNo)> 20 THEN NULL ELSE CaseNo END,CheckNo = CASE WHEN LEN(CheckNo)> 20 THEN NULL ELSE CheckNo END...Comments=CASE WHEN LEN(CaseNo)> 20 THEN 'The case no for this entry is .....' ELSE '' END + CASE WHEN LEN(CaseNo)> 20 THEN 'The case no for this entry is .....' ELSE '' END+... other columnsFROM table t... ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-10-24 : 11:45:05
|
| Since i see that you are using a "+" in the formula for the value of the "Comments" column, is that, in fact the "Append" command in SQL?? From what i've read so far, using the Append command on anything other than a varchar or nvarchar field (specifically, using it on a text field) will not work. And, since i failed to mention that my "comments" column is a text value, won't this fail when trying to append data to it?Just want to make sure before i re-code everything.thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-24 : 11:50:50
|
quote:
Originally posted by WJHamel
Since i see that you are using a "+" in the formula for the value of the "Comments" column, is that, in fact the "Append" command in SQL. From what i've read so far, using the Append command on anything other than a varchar or nvarchar field (specifically, using it on a text field) will not work. And, since i failed to mention that my "comments" column is a text value, won't this fail when trying to append data to it?Just want to make sure before i re-code everything.thanks
if its text field you need to use .WRITE to append data------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-24 : 11:54:41
|
| or another way is to cast to varchar(max) and use them directly. Also please keep in mind that text datatype is now deprecated so if possible use varchar(max) instead------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-10-24 : 12:16:12
|
| Yes. this database was written in SQL 2005 prior to our agency adopting 2008. It's one of those things they should have paid more attention to when we upgraded. I'll be adding the cast and convert statements to get this done.Thanks again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-24 : 12:18:11
|
quote:
Originally posted by WJHamel
Yes. this database was written in SQL 2005 prior to our agency adopting 2008. It's one of those things they should have paid more attention to when we upgraded. I'll be adding the cast and convert statements to get this done.Thanks again.
even in sql 2005 you've varchar(max)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2011-10-24 : 12:52:52
|
| I know, but was the Text datatype deprecated even in 2005? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-24 : 13:37:21
|
| yep. it wasseehttp://msdn.microsoft.com/en-us/library/ms187993(v=SQL.90).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Appending data in a column?