Christian Eriksson Christian Eriksson - 1 year ago 51
SQL Question

How does one automatically insert the results of several function calls into a table?

Wasn't sure how to title the question but hopefully this makes sense :)

I have a table (OldTable) with an index and a column of comma separated lists. I'm trying to split the strings in the list column and create a new table with the indexes coupled with each of the sub strings of the string it was connected to in the old table.


index | list
1 | 'a,b,c'
2 | 'd,e,f'

index | letter
1 | 'a'
1 | 'b'
1 | 'c'
2 | 'd'
2 | 'e'
2 | 'f'

I have created a function that will split the string and return each sub string as a record in a 1 column table as so:

SELECT * FROM Split('a,b,c', ',', 1)

Which will result in:

index | string
1 | 'a'
1 | 'b'
1 | 'c'

I was hoping that I could use this function as so:

SELECT * FROM Split((SELECT * FROM OldTable), ',')

And then use the id and string columns from OldTable in my function (by re-writing it slightly) to create NewTable. But I as far as I understand sending tables into the function doesn't work as I get: "Subquery returned more than 1 value. ... not premitted ... when the subquery is used as an expression."

One solution I was thinking of would be to run the function, as is, on all the rows of OldTable and insert the result of each call into NewTable. But I'm not sure how to iterate each row without a function. And I can't send tables into the a function to iterate so I'm back at square one.

I could do it manually but OldTable contains a few records (1000 or so) so it seems like automation would be preferable.

Is there a way to either:

  1. Iterate over OldTable row by row, run the row through Split(), add the result to NewTable for all rows in OldTable. Either by a function or through regular sql-transactions

  2. Re-write Split() to take a table variable after all

  3. Get rid of the function altogether and just do it in sql transactions?

I'd prefer to not use procedures (don't know if there is a solutions with them either) mostly because I don't want the functionality inside of the DB to be exposed to the outside. If, however that is the "best"/only way to go I'll have to consider it. I'm quite (read very) new to SQL so it might be a needless worry.

Here is my Split() function if it is needed:

@string nvarchar(4000),
@delimitor nvarchar(10),
@indexint = 0
RETURNS @splitTable TABLE (id int, string nvarchar(4000) NOT NULL) AS
DECLARE @startOfSubString smallint;
DECLARE @endOfSubString smallint;

SET @startOfSubString = 1;
SET @endOfSubString = CHARINDEX(@delimitor, @string, @startOfSubString);

IF (@endOfSubString <> 0)
WHILE @endOfSubString > 0
INSERT INTO @splitTable
SELECT @index, SUBSTRING(@string, @startOfSubString, @endOfSubString - @startOfSubString);

SET @startOfSubString = @endOfSubString+1;
SET @endOfSubString = CHARINDEX(@delimitor, @string, @startOfSubString);

INSERT INTO @splitTable
SELECT @index, SUBSTRING(@string, @startOfSubString, LEN(@string)-@startOfSubString+1);


Hope my problem and attempt was explained and possible to understand.

Answer Source

You are looking for cross apply:

SELECT t.index, s.item
     (dbo.split(t.list, ',')) s(item);

Inserting in the new table just requires an insert or select into clause.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download