Eminently possible (SPs and FUNCTIONs): http://msdn.microsoft.com/en-us/library/ms175007.aspx

Example C from that page:

CREATE TYPE LocationTableType AS TABLE

( LocationName VARCHAR(50)

, CostRate INT )

There's also an...

Jeff Moden (7/1/2013)

---

dwain.c (6/30/2013)

---

Perhaps you'd rather use a Quirky Update (QU)?

declare @t table

(

id int PRIMARY KEY,

PayCode char(2) null,

Amount decimal(15,2) null,

CDate date,

TranSeq int null

)

insert into @t

select 1,...

mickyT (7/1/2013)

---

Hi

A while back I got told by someone to avoid using additional functions on the XML if possible as they can have a big impact on the performance.

For example...

Perhaps you'd rather use a Quirky Update (QU)?

declare @t table

(

id int PRIMARY KEY,

PayCode char(2) null,

Amount decimal(15,2) null,

CDate date,

TranSeq int null

)

insert into @t

select 1, 'IR' , 1000.00...

Another way:

DECLARE @Date int = 1130627 ,--= (exclude the first digit)(13-06-27)

@Time int = 51458 --(24hr) = 5:14:58

SELECT DATEADD(hour, @Time/10000

,DATEADD(minute, @Time/100%100

,DATEADD(second, @Time%100, STUFF(STUFF(@Date+19000000,...

I was kind of curious how Luis's method would compare to something like this (using subqueries):

SELECT CaseID, Name

,PrimaryCodes=STUFF((

SELECT...

Mr Reddy,

You are in a much better position to determine which solution performs better, as I do not have access to your "millions" of member rows and "thousands" of receipt...

uravindarreddy (6/21/2013)

---

HI dwain!!!

Could you help me in generating script with CURSOR?

Even with CURSOR it is bit tricky.

I am not able to find a perfect script for different kinds of data...

I'm not sure if you can do it without a loop.

I'd probably put a CURSOR on the receipts table and allocate each receipt using the rCTE within that loop.

However I'll...

Interesting problem!

Unfortunately I don't have the time to take it through to conclusion, however here is a start. It will allocate the first receipt.

WITH Matching AS (

...

After correcting the name of the table on your second insert and adding a couple of SELECT statements, I get these results:

SELECT * FROM dbo.Receipt;

SELECT * FROM dbo.MemberPremiumDet;

ReceiptID PolicyID ReceiptAmt

1...

This was what I was working on before I had the brilliantly silly idea that my previous submission would be functionally correct:

SELECT ID, Wage, Sector, tw

FROM (

...

ChrisM@Work (6/18/2013)

---

dwain.c (6/17/2013)

---

ChrisM@Work (6/17/2013)

---

Hi Amy

This runs about 4 times faster than the original against the sample data set.

SELECT

w.ID,

Wage = MIN(w.Wage),

Sector = MIN(w.Sector),

tw = MIN(a.tw)

FROM #WageData...

ChrisM@Work (6/17/2013)

---

Hi Amy

This runs about 4 times faster than the original against the sample data set.

SELECT

w.ID,

Wage = MIN(w.Wage),

Sector = MIN(w.Sector),

tw = MIN(a.tw)

FROM #WageData w

INNER JOIN...

Lynn Pettis (6/13/2013)

---

SQLRNNR (6/13/2013)

---

Brandie Tarvin (6/13/2013)

---

jasona.work (6/13/2013)

---

Why can I picture something like that actually being buried in the depths of the SQL / SSIS code?

One of those little "bored programmer"...