Update field with multiple Recordsets
-
I have a table like this coming off our mainframe....
Name Text Matt Hi Matt How are Matt You How can I update a NEW table like..
Name Text Matt Hi How are You I have code like this
DECLARE @Text varchar(500)
Update DataWarehouse.dbo.MfgOrderOperations
SET OperationDescription =
(
Select COALESCE CAST(RTrim(T.operation_text) AS varchar(65))
FROM FileDownloads.dbo.operatnstest ST
WHERE ST.plant = O.PlantNumber and ST.order_number = O.OrderNumber and ST.oper = O.Operation
 
FROM FileDownloads.dbo.operatnstest T inner join
DataWarehouse.dbo.MfgOrderOperations O ON
T.plant = O.PlantNumber and T.order_number = O.OrderNumber and T.oper = O.Operation
But ofcourse it doesn't like the substring returning multi results.
-
Something bad like this:
CREATE TABLE SourceTable(
_Name varchar(10)
,_Text varchar(10)
,id int IDENTITY(1,1)
)
GO
INSERT INTO SourceTable
(_name, _text)
SELECT 'Matt', 'Hi'
UNION ALL
SELECT 'Matt', 'How are'
UNION ALL
SELECT 'Matt', 'You'
UNION ALL
SELECT 'Alex', 'Hi'
UNION ALL
SELECT 'Alex', 'Not Bad'
GO
CREATE FUNCTION fn_SumText(@name varchar(10))
RETURNS varchar(8000)
AS
BEGIN
DECLARE
@msg varchar(8000)
SELECT @msg = ''
SELECT
@msg = @msg + _text
FROM
SourceTable
WHERE
_name = @name
ORDER BY
id
RETURN @msg
END
GO
SELECT DISTINCT
_name
,[Text] = dbo.fn_SumText(_name)
FROM
SourceTable
___________________________________________
It's very bad way, but works. For little tables - normal.
Another approach - recursion in one way or another.
Recursive function or, if you on Yukon, recursive queries.
The most elegant - write you're own aggregate function. But this possible only on Yukon.
Hope this helps.
-
It's a much better approach than using recursion as it is the most direct approach to do it. TSQL, even using recursion, would still have to do the string concatenation... I'd stick with the supplied answer of
DECLARE @msg varchar(8000) SET @msg = '' SELECT @msg = @msg + _text FROM SourceTable WHERE _name = @name ORDER BY id
-
It's a much better approach than using recursion as it is the most direct approach to do it. TSQL, even using recursion, would still have to do the string concatenation... I'd stick with the supplied answer of
SELECT @var = @var + field
It is also recursion of some kind. Not obvious but in fact it's also recursion.
I'm using such code, but I do not like such approach. Such constructions falls in "Use on your own risk" category. In fact such it's even undocumented.
And it's obvious that for large tables performance will be terrific.
But we have not aggregate functions for strings, so on SQL2K it's just only possible.
I'm looking for Yukon. I hope for possibility extend aggregate functions list. Maybe I'm wrong.
-
I can see how the definition appears recursive, but it would be implemented and function as an interative process.
For example, you can calculate the sum of a series as
for i = 0 to 10
result := result + i
print i
or
function sumToZero(sum :integer; x :integer) :integer
if x = 0 then
result := sum
else
result := sum + sumToZero(sum, x - 1)
print sumToZero(10)
Both give the same result (I hope - had a brain freeze on the recursive fn - haven't read one so simple in a while :blush
but I am sure one will perform faster than the other.I have read a tiny bit about functions and what you can do in Yukon - perhaps the best thing would be to get your recordset in .NET and let .NET iterate through it and perform the string concatenation faster than TSQL - the logic is still clear and you would get even better performance...
I also think it is documented - have read it somewhere ages ago when looking for something else in SQL Server - will dig around a bit later and try to find it.
Cheers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply