SQL Merge Data

I have 2 tables, one which records tracking notes, the other only displays the Problem Note.
Please help, this is what I'm trying to achieve:
Table 1 is called CaseProgress has mulitple progress notes called case_progressnote with time stamps;
Table 2 is called Cases and has a table field called case_ProblemNote

I would like to merge all the case_progressnote values from the CaseProgress table into the case_ProblemNote field in the Cases table also inserting the the Case_assigneduserid and timestamp.

I have attached a text file with the results of SQL select statement from the 2 tables (CaseProgressTBL-CaseTBL.txt):

SELECT Case_CaseProgressID, Case_caseID, Case_ProblemNote, Case_ProgressNote, Case_createddate,
Case_TimeStamp FROM CaseProgress
WHERE Case_CaseId = '72'

Select Case_CaseId, Case_ProblemNote from Cases
WHERE Case_CaseId = '72'

I would like the result to appear in the Cases - Case_ProblemNote field as follows:
Test Tracking Problem Details.

UserID-5 Timestamp: 2011-03-01 20:13:24.000
Queue Stage Tracking Note 1

UserID-5 Timestamp: 2011-03-01 20:13:48.000
Investigating Tracking Note 2

UserID-5 Timestamp: 2011-03-01 20:14:01.000
Waiting Tracking Note 3

UserID-5 Timestamp: 2011-03-01 20:15:08.000
Queue Tracking Note 4

UserID-5 Timestamp: 2011-03-01 20:15:27.000
Waiting Tracking Note 5

UserID-5 Timestamp: 2011-03-01 20:15:49.000
Queued Tracking Note 6

UserID-5 Timestamp: 2011-03-01 20:15:49.000
Re-Assigned Tracking Note 7

Who is Participating?
SonderendConnect With a Mentor Author Commented:
I have a working solution now, looks like I was going about this wrong - thinking I need to create a function in SQL to do the required updates and call the function from the application (Sage CRM) via table level script.  This apparently is not possible and the function needs to be created using the systems existing table level script functionality.

Script as follows for your info:

function PostInsertRecord()


var timestampStr = Date().toString();
var userFirst = eWare.GetContextInfo("user", "user_firstname");
var userLast = eWare.GetContextInfo("user", "user_lastname");

var  caseProgRec = eWare.FindRecord("CaseProgress", WhereClause);
var caseID = caseProgRec.case_caseid;
var progressNote = caseProgRec.case_progressNote;
if(progressNote != undefined) {
var caseRec = eWare.FindRecord("cases", "case_caseid="+caseID);
var existingNote = caseRec.case_problemnote;
existingNote +="\n";
existingNote += "\n ";
existingNote += timestampStr + " : ";
existingNote += "[" + userFirst + " " + userLast + "] ";
existingNote += progressNote;
strSQL = "UPDATE Cases Set case_problemnote='" + existingNote;
strSQL += "' WHERE case_caseid=" + caseID;


I'm new to experts exchange and it appears I would need to allocate points for your assistance (MelindaJohnson & reb73) which I am happy to do and appreciate the assistance, but how and how many do I allocate.  What would happen if I click accepted solution, do the points go to me??

MeLindaJohnsonConnect With a Mentor Commented:

create FUNCTION [dbo].[getProgressNotes]
  (@caseid int )
RETURNS varchar(4000)
  DECLARE @result varchar(4000)
  declare @out varchar(10)
  SET @result = ''

SELECT @result =  @result + 'UserId-' + rtrim(cast(Case_AssignedUserId as varchar(3))) + ' Timestamp: ' + cast(case_timestamp as varchar(20)) + char(13) +  case_progressNote + char(13)
            FROM caseprogress WHERE case_caseID = @caseID


update cases
    set  cases.progress_note = case_progressNote + char(13) +  dbo.getProgressNotes (case_caseid)
from cases

be sure to test this before implementing in production since this is an update statement
where case_caseid = 72
SonderendAuthor Commented:
MeLindaJohnson,  thanks for replying to my post.  I will try this on a test environment a bit later today and feed back.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

SonderendAuthor Commented:
I just tried the create function in a new query and it is returning the following message:

Msg 402, Level 16, State 1, Procedure getProgressNotes, Line 11
The data types varchar and ntext are incompatible in the add operator.

Not sure which data types or fields it is referring to?  Do you need any more info?
Just helping out, change the select statement within the function as follows and retry -

SELECT @result =  @result
			+ 'UserId-' + rtrim(cast(Case_AssignedUserId as varchar(3))) 
			+ ' Timestamp: ' + cast(case_timestamp as varchar(20)) + char(13) 
			+  cast(case_progressNote as nvarchar(max)) + char(13)
FROM caseprogress WHERE case_caseID = @caseID

Open in new window

change the function so that the variables are nvarchar and not varchar
SonderendAuthor Commented:
Hi reb73, thanks for helping out, I will give this a go and feed back.
SonderendAuthor Commented:
Hi, changing the varchar to nvarchar worked and creates the function, thanks.

I am however struggling with the update script;
Original -
update cases
    set  cases.progress_note = case_progressNote + char(13) +  dbo.getProgressNotes (case_caseid)
from cases
where case_caseid = 72

My Version -
update cases
    set  cases.Case_ProblemNote = case_progressNote + char(13) +  dbo.getProgressNotes (case_caseid)
from cases
where case_caseid = 72

Error message:
Msg 207, Level 16, State 1, Line 2
Invalid column name 'case_progressNote'.

Which is true, because case_progressNote is the field on the CaseProgress table not Cases.
Would this update statement need a join? How would I script the join, left outer?
Probably should be case_PROBLEMnote?
I don't know.  I've never asked a question.  It doesn't matter for me.
SonderendAuthor Commented:
Okay thanks for the help anyway.
Since you did not use any of the solutions provided by others, you will not be foregoing any points as such..

If you think the experts' comments  did help, it would be nice to give them assist points. But its totally upto you unless there is an explicit objection from the contributing experts.

In this specific case I feel Melinda deserves some points for having made an earnest attempt to assist you and followed up as well, but its just my opinion!
SonderendAuthor Commented:
Hi reb73,
I agree, I would like to give some points to you both, but how and how many do I allocate?

This is my first question and first time of having to allocate points.

No need to give me any points, I just proferred a correction (in case you were stuck) and didn't really contribute much in terms of a solution.

You should be able to edit and re-allocate points, but just in case I'll raise an objection so that a moderator may help out.

I'd recommend that your accepted solution be retained with an assist for Melinda's post (35014135)
Starting closing process on behalf of the asker.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.