CODEWITHSUNDEEP
sql-serverinsert
Mitch
Mitch

Reputation: 2551

SQL Server Insert Query help

I have the following code that I use in a Query to update a table called ListClientCasePresentingIncidents which uses a function called fnSplit to split a comma seperated nvarchar named @CasePresentingIncidents.

   IF ( @CasePresentingIncidents <> '' ) 
    INSERT  INTO ListClientCasePresentingIncidents
            ( PresentingIncident ,
              CaseID
            )
            SELECT  SplitText AS PresentingIncident ,
                    @CaseID
            FROM    fnSplit(@CasePresentingIncidents)

All of this works really well one record at a time, passing in a value for @CaseID and @CasePresentingIncidents.

The question is, how would I modify this code so that it took values from a table called ClientCase rather than 2 parameters? It would need to replace @CaseID and @CasePresentingIncidents with the values from ClientCase.CaseID and ClientCase.CasePresentingIncidents. Many thanks.

Upvotes: 1

Views: 247

Answers (1)

Andriy M
Andriy M

Reputation: 77657

Basically, I can see two ways of solving this:

  1. Combine (concatenate) all the ClientCase.CasePresentingIncidents values that are to be processed, into one string and pass it as an argument to fnSplit() (i.e. dbo.fnSplit()?). Depending on what version of SQL Server you are using you might consider different ways of concatenating. If you need some guidance here, I think this could be a good starting point: Concatenating Row Values (There many similar questions related to it and listed accordingly in the Related section.)

  2. Use a cursor to call dbo.fnSplit() for every row of ClientCase. I should probably suggest creating a new table-valued function dbo.fnSplitMultiple() which would implement this approach. In your example then you would select from dbo.fnSplitMultiple() instead of dbo.fnSplit().

If the resulting string can be very long, then maybe the first method, though otherwise probably more attractive, wouldn't be very appropriate. I haven't had much experience with handling very long strings server-side, using SPs and functions too, so I'm not sure, and to me cursors here would be just a backup option.

Upvotes: 2

Related Questions