December 9, 2014 at 5:16 am
Hi Guys,
I've been having trouble merging NOTES in the one table depending on the contents of another table
The Contsupp table contains a NOTES column and a RECID column
The CodedUp table contains RECID and TAGRECID column
CodedUp.Recid = Contsupp.Recid
CodedUp.TagRecid indicates the master Contsupp.Recid field the Notes are to be merged into
I would like to merge the Contsupp.Notes fields depending on TagRecid Field in Codedup
In my example below, The results should look like this:
NOTESRecid
1MULTILINE NOTES1
2MULTILINE NOTES
3MULTILINE NOTES3
4MULTILINE NOTES
5MULTILINE NOTES
6MULTILINE NOTES6
7MULTILINE NOTES
Scripts to generate test data
CREATE TABLE #CONTSUPP
(NOTES TEXT,
RECID VARCHAR(15))
CREATE TABLE #CODEDUP
(RECID VARCHAR(15) NOT NULL,
TAGRECID VARCHAR(15) NOT NULL)
INSERT INTO #CONTSUPP
SELECT '1MULTILINE NOTES', '1' UNION ALL
SELECT '2MULTILINE NOTES', '2' UNION ALL
SELECT '3MULTILINE NOTES', '3' UNION ALL
SELECT '4MULTILINE NOTES', '4' UNION ALL
SELECT '5MULTILINE NOTES', '5' UNION ALL
SELECT '6MULTILINE NOTES', '6' UNION ALL
SELECT '7MULTILINE NOTES', '7'
INSERT INTO #CODEDUP
SELECT '1', '1' UNION ALL
SELECT '2', '1' UNION ALL
SELECT '3', '3' UNION ALL
SELECT '4', '3' UNION ALL
SELECT '5', '3' UNION ALL
SELECT '6', '6' UNION ALL
SELECT '7', '6'
The closest I've come so far is the script below, Im more than happy for suggestions on better ways to merge the notes though.
DECLARE @col NVARCHAR(MAX);
SELECT @col= COALESCE(@col, '') + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CONVERT(NVARCHAR, NOTES)
FROM #CONTSUPP where RECID IN (
Select RECID from #CODEDUP WHERE TAGRECID = '1'
)
SELECT @col;
Thanks for your help
December 9, 2014 at 6:10 am
First is there a reason you are using TEXT? Why not VARCHAR(MAX)? From BOL
ntext, 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.
To merge a to a TEXT column you need different commands as it is not a character string. Look into UPDATETEXT, TEXTPTR, and WRITETEXT for a start.
December 9, 2014 at 6:56 am
djj (12/9/2014)
First is there a reason you are using TEXT? Why not VARCHAR(MAX)? From 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.
To merge a to a TEXT column you need different commands as it is not a character string. Look into UPDATETEXT, TEXTPTR, and WRITETEXT for a start.
The contsupp table has been around for many years.
Changing it would break other applications.
December 9, 2014 at 7:05 am
Don. (12/9/2014)
djj (12/9/2014)
First is there a reason you are using TEXT? Why not VARCHAR(MAX)? From 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.
To merge a to a TEXT column you need different commands as it is not a character string. Look into UPDATETEXT, TEXTPTR, and WRITETEXT for a start.
The contsupp table has been around for many years.
Changing it would break other applications.
Do you know it will break any applications or are you just assuming it will? I have changed a number of text columns to varchar(max) in place with legacy systems and there has been pretty much no issues at all. Any conversions or cast would be implicit and the syntax is identical for CRUD operations. Might be worth trying in your test environment.
_______________________________________________________________
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/
December 9, 2014 at 7:08 am
Sean Lange (12/9/2014)
Don. (12/9/2014)
djj (12/9/2014)
First is there a reason you are using TEXT? Why not VARCHAR(MAX)? From 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.
To merge a to a TEXT column you need different commands as it is not a character string. Look into UPDATETEXT, TEXTPTR, and WRITETEXT for a start.
The contsupp table has been around for many years.
Changing it would break other applications.
Do you know it will break any applications or are you just assuming it will? I have changed a number of text columns to varchar(max) in place with legacy systems and there has been pretty much no issues at all. Any conversions or cast would be implicit and the syntax is identical for CRUD operations. Might be worth trying in your test environment.
Any application that uses the "TEXT" functions would break. Having went from TEXT to VARCHAR(MAX), I think it is worth the trouble to convert. Besides by converting now, you will not HAVE to convert in the future.
December 9, 2014 at 7:22 am
Thank you for your suggestions.
Changing the Contsupp table is not an option.
While the following script doesnt work as I would like.
I am able to update the Contsupp.Notes column.
UPDATE CS
Set Notes = (CASE WHEN NOTES IS NULL THEN (Select NOTES FROM CONTSUPP WHERE RECID = CUP.RECID)
ELSE substring(NOTES, 1, DATALENGTH(NOTES)) + CHAR(13) + CHAR(10) + (Select SUBSTRING(NOTES, 1, DATALENGTH(NOTES)) FROM CONTSUPP WHERE RECID = CUP.RECID)
END)
from CONTSUPP CS
RIGHT JOIN CODEDUP CUP ON CS.RECID = CUP.TAGRECID
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply