Select All Records from Temporary Table and Assign to VARCHAR?

Question

Post reply

Select All Records from Temporary Table and Assign to VARCHAR?

Winnie_Too

SSC Veteran

Points: 256
More actions
July 17, 2013 at 5:17 am

#296485

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Here's a fairly complete article on how to concatenate row values using different solutions:

Concatenating Row Values in Transact-SQL[/url]

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Winnie_Too SSC Veteran Points: 256 More actions · Answer 2

Winnie_Too

SSC Veteran

Points: 256

July 17, 2013 at 5:25 am

#1633184

Thank you!

I will read it straight away.

Winnie_Too SSC Veteran Points: 256 More actions · Answer 3

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 SSC-Insane Points: 21316 More actions · Answer 4

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 SSC Guru Points: 183698 More actions · Answer 5

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 SSC Veteran Points: 256 More actions · Answer 6

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