how to write sp insert query for this table with out identity storing many data at a time with comma seperated value?

• Sivaganesh Tamilvendhan

  Hall of Fame

  Points: 3498

  More actions

  May 9, 2012 at 11:44 pm

  #392222

  create table tblSubCatData

  (

  Classid int primary key,

  SubCatId int,

  SubCatCpId int,

  SubCatDaId smallint,

  DataLevel smallint,

  IndexNumber smallint,

  DataText varchar(100),

  IsActive bit

  )

  this is the table

  in this

  SubCatDaId,DataLevel,DataText will have comma seperated data as input in parameter and following this i just want to generate indexnumber colun automatically

  here i got one function

  ----select * from [dbo].[Split]('1_2|3_5','|')

  ALTER FUNCTION [dbo].[Split](

  @delimited NVARCHAR(MAX),

  @delimiter NVARCHAR(100)

  ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))

  AS

  BEGIN

  DECLARE @xml XML

  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

  INSERT INTO @t(val)

  SELECT r.value('.','varchar(MAX)') as item

  FROM @xml.nodes('/t') as records(r)

  RETURN

  END

  which store with one only one var char value and i need three varchar value and if i insert this Classid should generate automatically without identity i tried like this

  DECLARE @ClasslD int

  DECLARE @test1 int

  set @test1=( Select COUNT(Classid) from dbo.tblSubCatData )

  select @test1

  SET @ClasslD = @test1 + 1

  select @ClasslD

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 10, 2012 at 8:31 am

  #1485735

  Your question is not very clear. I can however suggest a much fast string splitting function than the xml based version you posted. Take a look at the string splitter link in my signature.

  In the meantime if you can try to more clearly explain what you are trying to do I will be happy to help.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/