insert comma delimited values in to a table

• Satya js

  SSC-Addicted

  Points: 454

  More actions

  July 19, 2007 at 11:29 am

  #174515

  Hi,

  I have a comma delimited string with a length of more than 10,000 characters. I want to insert these comma delimited values into a table. How can I achieve this?

  Here is what I have tried. It works fine if the input string is less than 8000 characters.

  --user input

  DECLARE @RequestIDs varchar(8000)

  CREATE TABLE #t (ReqID int not null)

  DECLARE @sql VARCHAR(8000)

  SELECT @sql = 'insert into #t select '+

  REPLACE (@RequestIDs, ',' , ' union select ')

  -- Load values from comma delimited string into a table

  EXEC ( @sql )

  any help is highly appreciated.

  Thanks,

  Satya

• Daryl AZ

  Hall of Fame

  Points: 3087

  More actions

  July 19, 2007 at 11:39 am

  #720862

  I haven't test this idea but...

  DECLARE @RequestIDs varchar(MAX)

  luck

  Daryl

• Satya js

  SSC-Addicted

  Points: 454

  More actions

  July 19, 2007 at 11:44 am

  #720866

  Daryl,

  Thanks for the reply. Varchar has a limit of 8000 characters. I cannot specify more than that.

  I know I can use text data type. But the problem with "text" is that I cannot use "Replace" function as I am doing currently.

• Daryl AZ

  Hall of Fame

  Points: 3087

  More actions

  July 19, 2007 at 11:50 am

  #720868

  from BOL

  '

  Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.'

  Give it a shot

  usage

  Declare @EntryValue varchar(MAX) -- with the actual word MAX