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 |
OldMySQLUser
Constraint Violating Yak Guru
301 Posts |
Posted - 2010-02-15 : 06:51:27
|
I have an address table which has been checked by an outside agency for accuracy.So, I have two tables:originaladdress----------------clientid int,Forename varchar(15),Surname varchar(15),Address1 varchar(30),Address2 varchar(30),Address3 varchar(30),Address4 varchar(30),Address5 varchar(30),PostCode varchar(8)checkedaddress--------------clientid int,ForenameFlag char(1),SurnameFlag char(1),AddressFlag char(1),NewForename varchar(15),NewSurname varchar(15),NewAddress1 varchar(30),NewAddress2 varchar(30),NewAddress3 varchar(30),NewAddress4 varchar(30),NewAddress5 varchar(30),NewPostCode varchar(8)I wish to insert into a third tableDataNotes---------clientid int,notes textThe ForenameFlag, SurnameFlag and AddressFlag columns can contain either 'Y' or some other character.If these flag columns contain a character other than 'Y' then the notes portion of the entry will containan entry like to indicate the difference between the existing data in table originaladdress and the new data in checkedaddress like so:'Forename differs: Current record = Bill. New record = David + char(13) + char(10) Surname differs: Current record = Smith. New record = Brown + char(13) + char(10) Address differs: Current record = xxxxxxxxxxxxxx. New record = yyyyyyyyyyyyyyy' The three lines shown above are all in the SAME record entry. How can I do this please? |
|
Kristen
Test
22859 Posts |
Posted - 2010-02-15 : 07:39:04
|
[code]INSERT INTO DataNotes(clientid, notes)SELECT [clientid], [notes] = LEFT([notes], DATALENGTH([notes])-2) -- Chop off trailing CR/LFFROM( SELECT [clientid] = O.clientid, [notes] = CASE WHEN ForenameFlag = 'Y' THEN '' ELSE 'Forename differs: Current record = ' + COALESCE(O.Forename, '[NULL]') + '. New record = ' + COALESCE(N.Forename, '[NULL]') + char(13) + char(10) END + CASE WHEN SurnameFlag = 'Y' THEN '' ELSE ... FROM originaladdress AS O JOIN checkedaddress AS N ON N.clientid = O.clientid) AS XWHERE X.[notes] <> ''[/code] |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-15 : 07:41:39
|
"DataNotes---------clientid int,notes text"[notes] needs to be VARCHAR(MAX) - and not TEXT datatype |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-02-15 : 07:45:57
|
and the reason is stated in BOLntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.MadhivananFailing to plan is Planning to fail |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-15 : 07:52:52
|
Yeah ... actually I was thinking that if the concatenation is more than 8000 characters I didn't think it would store in a TEXT - but maybe it will be created in a VARCHAR(MAX), as an intermediate variable, and then CAST to a TEXT ?So maybe it will work ... until TEXT is deprecated |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-02-15 : 07:56:21
|
quote: Originally posted by Kristen Yeah ... actually I was thinking that if the concatenation is more than 8000 characters I didn't think it would store in a TEXT - but maybe it will be created in a VARCHAR(MAX), as an intermediate variable, and then CAST to a TEXT ?So maybe it will work ... until TEXT is deprecated
We can't assume until it is well documented MadhivananFailing to plan is Planning to fail |
|
|
OldMySQLUser
Constraint Violating Yak Guru
301 Posts |
Posted - 2010-02-15 : 08:36:01
|
Many thanks Kristen |
|
|
|
|
|
|
|