How can I insert multiple split strings in single insert query

Question

  Declare @APS nvarchar(1000)='1,1,1.79103540163304,1.79103540163304'
          ,@cluster nvarchar(1000)='0150,0019,0150,0019'
          ,@style nvarchar(1000)='696707-018,696707-018,696707-017,696707-017'

     CREATE TABLE #temptable (
      ID int IDENTITY (1, 1) NOT NULL ,
      stylecolor varchar (500) NOT NULL ,
      APSDev varchar (250)  NULL,
      ClusterID varchar(1000) Null
     )

Here I would like to insert

  insert into #temptable  (stylecolor, ClusterID, APSDev)
  select  item  from  [<table name>]. dbo.SplitString(@style,',') 
  select  item  from  [<table name>].dbo.SplitString(@cluster,',')
  select  item  from  [<table name>]. dbo.SplitString(@APS,',')

Getting error:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

Output Result: should be looks like this

  stylecolor  clusterID      APSDev
  696707-018    0150            1
  696707-018    0019             1
  696707-017    0150             1.79103540163304
  696707-017    0019             1.79103540163304

what is your expected result in the temptable? how will the data look like? — jose_bacoy, May 02 '18 at 22:11
As far as I know SplitString is not a built in function; though I can find an implementation in another SO question. — Uueerdo, May 02 '18 at 22:15

score 0 · Answer 1 · answered May 02 '18 at 22:31

If your SplitString function is something like the one found here, you can probably make a modified version that returns a table with a two fields, list_index and list_value. Then use a query like:

INSERT INTO #temptable  (stylecolor, ClusterID, APSDev)
SELECT s.list_value, c.list_value, a.list_value
FROM dbo.SplitStringWithIndex(@style,',') AS s
INNER JOIN dbo.SplitStringWithIndex(@cluster,',') AS c ON s.list_index = c.list_index
INNER JOIN dbo.SplitStringWithIndex(@APS,',') AS a ON s.list_index = a.list_index
;

Modifying the one I linked should involve little more than adding a counter in it's main loop.

How can I insert multiple split strings in single insert query

1 Answers1