SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select All Records from Temporary Table and Assign to VARCHAR?


Select All Records from Temporary Table and Assign to VARCHAR?

Author
Message
Winnie_Too
Winnie_Too
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11
Hello!
I am new to TSQL and require some help with a problem I'm having.

I have a stored procedure where I create a temporary table:

Create Table #TempSourceOutput(
SourceKey varchar(50),
SourceID varchar(50),
SourceComment varchar(max) )

and I can successfully populate it using:
Insert Into #TempSourceOutput (SourceKey, SourceID, SourceComment)
Select @SourceKey, Cast(@SourceID as varchar), @SourceComment

however, I would like to get all the values from this table and list them (along with other variables) in a varchar, resulting in something like this:
Declare @TargetData varchar(max)
Set @TargetData = "Target Project Name"
Set @msg= 'Are you sure you wish to copy the following records to: ' + @TargetData + '?' +
'AA 12 Test Comment'
'AB 13 Another Test Comment'
'AC 14 And another one...'

(where lines above AA, AB, AC are the result set of #TempSourceOutput - ie. columns SourceKey + SourceID + SourceComment)

I would like to return it all as one complete varchar.
Is it at all possible, and if so, how.
Many thanks for your help in advance.

Best wishes
Winnie
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27623 Visits: 13268
Here's a fairly complete article on how to concatenate row values using different solutions:

Concatenating Row Values in Transact-SQL


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Winnie_Too
Winnie_Too
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11
Thank you!
I will read it straight away.
Winnie_Too
Winnie_Too
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11
Thank you (again) for your speedy reply.

Using this bit of code:
SELECT DISTINCT [Source Data]
FROM #TempSourceOutput t1
CROSS APPLY ( SELECT SourceKey + ' [' + SourceID +'] '+ SourceComment
FROM #TempSourceOutput t2
WHERE t2.SourceID = t1.SourceID
ORDER BY SourceKey
FOR XML PATH('') ) D ( [Source Data] )

I get the results:

Source Data
BOOKING [2000182877] Test blabla
BOOKING [2000182878] Testabc1111


which is a start but how do I retrieve the above values and list them (along with other variables) in a varchar, like this:
Declare @TargetData varchar(max)
Set @TargetData = "Target Project Name"
Set @msg= 'Are you sure you wish to copy the following records to: ' + @TargetData + '?' +
'BOOKING [2000182877] Test blabla'
'BOOKING [2000182878] Testabc1111'

I would be very grateful if you could offer some more assistance!
Thanks,
Winnie
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3105 Visits: 2766
can you please provide some sample data with resultant output.......

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16705 Visits: 19108
You might want to try something like this
Declare @msg varchar(8000),
@TargetData varchar(max)
Set @TargetData = "Target Project Name"
Set @msg= 'Are you sure you wish to copy the following records to: ' + @TargetData + '?'

SELECT @msg = @msg + CHAR(10) + SourceKey + ' [' + SourceID +'] '+ SourceComment
FROM (
SELECT DISTINCT SourceKey, SourceID, SourceComment
FROM #TempSourceOutput t2) t
ORDER BY SourceKey




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Winnie_Too
Winnie_Too
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11
Thank you both kapil_kk & Luis Cazares,
I'm sorry I have been unexpectedly offline for a few days so apologies for the late reply.

I managed in the end and it wasn't too difficult either.

Here is what I did:

I populated the table like so:

--Concatenate source data values and insert into temp table, seperated by a $ sign
Insert Into #TempSourceOutput (All_Source_Data)
Select @SourceKey + ' [' + Cast(@SourceID as varchar) +'] '+ @SourceComment + '$'

Then I got each row from temp table #TempSourceOutput and placed it in one single row of data for use in message later

Declare @Txt1 varchar(MAX)
Declare @AllSourceData varchar (max)
Set @Txt1=''

Select @Txt1 = @Txt1 + All_Source_Data
From #TempSourceOutput
Select @AllSourceData = LEFT(@Txt1,LEN(@Txt1)-1)

Then I used the @AllSourceData variable in my message:

Set @msg= 'Are you sure you wish to copy ' + CAST(@NumberOfTransfers as varchar) + ' Objects to ' + @TargetKey + ' [' +
CAST(@TargetID as varchar) + '] ' + @TargetComment + '?' + CHAR(10)+CHAR(13) + Replace(@AllSourceData,'$',+
CHAR(10)+CHAR(13))

I'm sure it's not the most sophisticated of solutions, but it works for me.
Many thanks for your help.
Winnie
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search