Muliple Comma Seperated values insert into table without using cursor

• pvuser.chennai

  Grasshopper

  Points: 10

  More actions

  January 27, 2017 at 6:41 am

  #326183

  DROP TABLE TestMode

  CREATE TABLE TestMode
  (
    Mode NVARCHAR(MAX),
      ANS NVARCHAR(MAX)
  )

  DECLARE @mode NVARCHAR(MAX)
  SET @mode = 'Mode1|Mode2|Mode3'

  DECLARE @Ans NVARCHAR(MAX)
  SET @Ans = 'Ans1|Ans2|;Ans1|Ans2;|Ans1|Ans2'

  I need a Result like below

  Mode1 Ans1|Ans2
  Mode2 Ans1|Ans2
  Mode3 Ans1|Ans2

  Kindly help me how to query for the above

  i tried below its not working
  INSERT INTO TestMode (Mode) 
  (SELECT Item FROM dbo.SplitString(@mode,'|'))

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 27, 2017 at 7:01 am

  #1925315

  Twice in the same day 🙂 Try this thread.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  January 27, 2017 at 7:04 am

  #1925317

  pvuser.chennai - Friday, January 27, 2017 6:41 AM

  DROP TABLE TestMode

  CREATE TABLE TestMode
  (
    Mode NVARCHAR(MAX),
      ANS NVARCHAR(MAX)
  )

  DECLARE @mode NVARCHAR(MAX)
  SET @mode = 'Mode1|Mode2|Mode3'

  DECLARE @Ans NVARCHAR(MAX)
  SET @Ans = 'Ans1|Ans2|;Ans1|Ans2;|Ans1|Ans2'

  I need a Result like below

  Mode1 Ans1|Ans2
  Mode2 Ans1|Ans2
  Mode3 Ans1|Ans2

  Kindly help me how to query for the above

  i tried below its not working
  INSERT INTO TestMode (Mode) 
  (SELECT Item FROM dbo.SplitString(@mode,'|'))

  Given the desired results, you need to split on the semicolon, not the pipe character.
  😎