loop through temporary table in SQL

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Jigsaw:

Do you want the concatenated list ordered? If so, how?

|||

Jigsaw:

Here is a way to do this using a CTE:

declare @.mockup table
(ID INT NOT NULL ,PK INT , pocket VARCHAR(10))

insert into @.mockup
select 1, 1, 'p1' union all
select 2, 1, 'p2' union all
select 3, 2, 'p3' union all
select 4, 2, 'p4' union all
select 5, 3, 'p5' union all
select 6, 3, 'p6' union all
select 7, 4, 'p7' union all
select 8, 5, 'p1' union all
select 9, 5, 'p2' union all
select 10,5, 'p83'
--select * from @.mockup

declare @.output table (PK int, pocketList varchar(200))

;with pocketList as
( select row_number () over
( partition by PK
order by pocket
) as seq,
row_number () over
( partition by PK
order by pocket desc
) as revSeq,
PK,
ID,
pocket
from @.mockup
), recursivePockets as
( select seq,
revSeq,
PK,
pocket,
cast(pocket as varchar(max)) as [product]
from pocketList
where revSeq = 1
union all
select b.seq,
b.revSeq,
b.PK,
b.pocket,
cast(b.pocket + ', ' + product as varchar(max))
from recursivePockets a
inner join pocketList b
on b.revSeq = a.revSeq + 1
and b.pk = a.pk
)

insert into @.output
select pk,
product
from recursivePockets
where seq = 1
order by pk

-- pk product
-- -- -
-- 1 p1, p2
-- 2 p3, p4
-- 3 p5, p6
-- 4 p7
-- 5 p1, p2, p83

( Ooops! Almost forgout to load it into an output table; sorry )

|||Have a read of FOR XML

Example

SELECT ' '+rtrim(ltrim(pocket))
from mytable
where pk = 2
order by pocket
FOR XML PATH('')

This will return you 1 row with a space in between each item you want to concat

Looking for suggestions

I have two stored procedures (l'll call them P1 & P2). P1, after a lot of processing, creates a temporary table that is used by P2 after an "exec P1" is done. I've separated the logic into two stored procedures because, ultimately, other sprocs will need the output of P1.

I get an error if I use #tempTable as the output table in P1 because it no longer exists after P1 finishes. ##tempTable works, but I'm concerned about concurrency issues. Any suggestions on what construct(s) I should be using?

Thanks in advance!

Global temporary table (GTT) (##tempTable) might not be the one you need

here is the downside of using GTT.

1. you cannot use it to cache Data for a long period of time - let say 1 day

If all the reference to GTT is drop then the data in it is also lost forever

2. GTT is visible to all stored procedure using it - thus concerrency issue

This wont cause you any problem if the data in it is readonly. meaning

You process only once for the entire day. if you'll be cjhanging the content

of GTT from time to time this will cause you headache

two recommendations:

1. if the resultset of P1 will have to live for at least 1 day and there will be no processing required for that span of time,

I would recommend a physical table instead.

2. if the result set of P1 is dynamic and P1 needs to be reused from time to time

I recommend that P1 be transformed into a table-valued function

|||

I totally agree. In fact, I would probably suggest (based on the phrase "lot of processing") that you create a couple of permanent tables. One to hold the results, and the other to keep either:

1. Users of the data that you expect to read the data. As these users read the data, delete their rows, when the last reader finishes, delete them.

2. An amount of time before the results are invalidated. So if you could run it daily, have it be invalidated at midnight, and then users of the data would do their select, and if no data was there, or it was past it's date, it would build the cache, and if the data was there, then fetch it.

If a lot of processing is not really a "lot" then you might use a table-valued function, but there are limitations, like no side effects, if there is any data being written in the process.