July 9, 2014 at 5:31 am
Hi All,
I have a table valued function that splits a string based on a delimiter and produces a set of discrete values. The function:
ALTER FUNCTION [dbo].[Split] (@sep char(1), @s-2 varchar(8000))
RETURNS table
AS
RETURN (
WITH splitter_cte AS (
SELECT CHARINDEX(@sep, @s-2) as pos, 0 as lastPos
UNION ALL
SELECT CHARINDEX(@sep, @s-2, pos + 1), pos
FROM splitter_cte
WHERE pos > 0
)
SELECT SUBSTRING(@s, lastPos + 1,
case when pos = 0 then 80000
else pos - lastPos -1 end) as OutputValues
FROM splitter_cte
) [/b]
The output:
OutputValues
1
2
3
4
5
6
My question is how best to use these values in my next statement, which will do inserts. I was thinking to avoid a cursor, but I'm not sure how best to proceed.
Thanks!
Bill
July 9, 2014 at 5:54 am
Quick thought, place the function in a cte and use the output in the insert.
😎
July 9, 2014 at 5:55 am
Treat it as any other table source: SELECT * FROM dbo.MyFunction(param, param)
Having said that, the rCTE string splitter is known to perform poorly against other methods. You can read about it in this article [/url]which offers a much faster alternative.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 9, 2014 at 6:16 am
How do I do an insert for each row, without iterating over the available rows?
July 9, 2014 at 6:23 am
bill.warner7 (7/9/2014)
How do I do an insert for each row, without iterating over the available rows?
if runtime temp table is ok then you can use below:
SELECT * INTO #tempTable FROM dbo.MyFunction(param, param)
If table is already present with same return values (columns) then you can use:
INSERT INTO TABLE (column1) SELECT column1 FROM dbo.MyFunction(param, param)
Thanks
July 9, 2014 at 6:23 am
INSERT INTO TargetTable (<<columnlist>>)
SELECT <<columnlist>>
FROM SourceTable
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply