Help with inserting multiple records using a CSV value.

I have the Temporary table:

ItemDetailID (int)
FieldID (int)
FieldTypeID (int)
ReferenceName (Varchar(250))
[Value] (varChar(MAX))

in one instance Value might equal: "1, 2, 3, 4"

This only happens when FieldTypeID = 5.

So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the TableFieldListValues(ItemDetailID, [value])

I have created a function to split the [Value] into a table of INTs

Any Advice?

If your function returns a table type data, loop through the table and do an INSERT for each row.

|||

I would love to do that... but... I can program my way out of a box using C#... with SQL.. i could probably take a baby step to the bathroom :\

Do you know of any links/resources/source that could show me how? I've googled like crazy, but no luck :(

|||

You could do an :

(1) Declare a table variable with an additional column Processed tinyint.

(2) INSERT INTO @.table

SELECT dbo.someFunction

(3) Loop through the table.

WHILE EXISTS (SELECT * FROM @.table Where Procesed = 0)

Begin

Get the values from the @.table

Insert into the Original table

update @.table set processed = 1 Where Condition

End

|||

I think I understand...

While Loops, So when you do the:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

It goes through it row by row, sort of like a Foreach(DataROw row in DataTable) in C#?

|||

RTernier:

I think I understand...

While Loops, So when you do the:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

It goes through it row by row, sort of like a Foreach(DataROw row in DataTable) in C#?

Yes.

|||

That would work. Now another question (Yea, I'm not that strong in SQL :P )

While I go through the WHILE loop,

Is there a way I can grab the values of the loop I'm going through?

Example:

WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

====

I could do this right:

WHILE EXISTS(SELECT * FROM @.Table T WHERE Processed = 0)
BEGIN

PRINT T.MyColumn
END

===

if not, how can I directly access the values from T?

|||

If the values returned by your function are unique, then you can use a MIN(Id) to get each id, else you can add an IDENTITY column to your table variable and use that to navigate through each row.

Decare @.rowid int

WHILE ...

Begin

SELECT @.rowid = MIN(id) FROM @.Table Where Processed = 0

INSERT INTO ...original table

Update @.t Set Processed = 1 Where Id = @.Rowid

End