Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select All Records from Temporary Table and Assign to VARCHAR? Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 6:35 AM
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
Post #1474494
Posted Wednesday, July 17, 2013 5:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 13,562, Visits: 11,372
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1474499
Posted Wednesday, July 17, 2013 5:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 6:35 AM
Points: 4, Visits: 11
Thank you!
I will read it straight away.
Post #1474502
Posted Wednesday, July 17, 2013 6:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 6:35 AM
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
Post #1474545
Posted Wednesday, July 17, 2013 7:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:24 AM
Points: 1,952, Visits: 2,396
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/
Post #1474584
Posted Wednesday, July 17, 2013 8:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:54 PM
Points: 3,937, Visits: 8,930
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1474664
Posted Monday, July 22, 2013 1:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 6:35 AM
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
Post #1475887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse