November 15, 2005 at 5:46 pm
I have a cursor that updates a temp table
but it takes a very long time.
I am looking for a set based solution.
If anyone can come up with one I would really appreciate it.
create table customer_orders
(customerid int not null
,ordernumber varchar(10) not null
)
go
ALTER TABLE [dbo].[customer_orders] ADD
CONSTRAINT [PK_customerid_ordernumber] PRIMARY KEY CLUSTERED
(
[customerid],
[ordernumber]
) ON [PRIMARY]
go
insert into customer_orders(customerid,ordernumber)
values (5,'ALK098671')
insert into customer_orders(customerid,ordernumber)
values (5,'ALR099231')
insert into customer_orders(customerid,ordernumber)
values (7,'RLR197562')
insert into customer_orders(customerid,ordernumber)
values (7,'ALA199535')
insert into customer_orders(customerid,ordernumber)
values (7,'BLM698773')
insert into customer_orders(customerid,ordernumber)
values (7,'CNR314181')
insert into customer_orders(customerid,ordernumber)
values (22,'ZBA262135')
insert into customer_orders(customerid,ordernumber)
values (22,'BLM698773')
insert into customer_orders(customerid,ordernumber)
values (22,'CNR314181')
I would like to insert all of this into a temp table so when I select * from temptable I get the following result.
5,ALK098671,ALR099231
7,RLR197562,ALA199535,BLM698773,CNR314181
22,ZBA262135,BLM698773,CNR314181
Thanks again for the help.
Gary
November 15, 2005 at 8:54 pm
--===== Create a function to concatenate orders by customer
CREATE FUNCTION dbo.ConcatOrderNum (@pCustomerID AS INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @oResult VARCHAR(8000)
SELECT @oResult= COALESCE(@oResult+',','')+OrderNumber
FROM Customer_Orders WITH (NOLOCK)
WHERE CustomerID = @pCustomerID
ORDER BY OrderNumber
RETURN @oResult
END
--===== If Temp table exists, drop it
IF OBJECT_ID('TempDB..#TempTable') IS NOT NULL
DROP TABLE #TempTable
--===== Create and populate the temp table
SELECT CAST(CustomerID AS VARCHAR)+','
+ dbo.ConcatOrderNum(CustomerID) AS CustAndOrders
INTO #TempTable
FROM Customer_Orders WITH (NOLOCK)
GROUP BY CustomerID
--===== Display the contents of the temp table
SELECT *
FROM #TempTable
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2005 at 9:43 pm
Mr. Moden, thank you for the quick reply.
I am not entirely clear on how the work is being done.
In the function it looks like the "COALESCE" function is doing the work.
I don't see a create statement for the temptable but yet it works. How is that possible?
Once again, thanks for your time.
Gary
November 15, 2005 at 10:49 pm
The "INTO" clause of SELECT/INTO creates the temp table on the fly.
You are correct about COALESCE... It does the work... you could replace it with ISNULL and the effect would be the same in this case because only two conditions are required.
The COALESCE decides if the value of the variable is null. If it is, it creates an empty '' string and then the OrderNumber is concatenated. If the first order number where 'ABC', then an empty string + 'ABC' = 'ABC' and that get's assigned to the variable.
Second value comes in from the table... let's say it's 'DEF'... The variable is NOT null at this point and contains 'ABC'. Since it's not null, it takes the first value of the COALESE which is the variable plus a comma or, in this case, 'ABC,' and concatenates the next order number which gives us 'ABC,' + 'DEF' or 'ABC,DEF'.
It continues as it did for the second value from the table for all other values, adding a comma to the variable and adding the next order number to that.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply