January 23, 2008 at 1:08 pm
Hi Guys,
I'm trying to insert a string into a temp table. For instance I want a nvarchar(4000) string
@Ids inserted as separte Ids in a All_id table.
The user can type in 1,2,3,4,5,6 etc separated by commas. How can I get that into my temp table as idividual Ids in a column?
Thanks guys
January 23, 2008 at 2:19 pm
Check if this will help you:
Create Table ##TempTable (Id nvarchar(5))
declare @Ids nvarchar(4000)
declare @tempstr nvarchar(4000)
select @Ids = '1,2,3,4,5,6'
select @tempstr = @Ids
WHILE len(@tempstr) > 0
BEGIN
--Printing the current temp string and the position of the first comma in it
Print @tempstr
Print patindex('%,%',@tempstr)
-- Inserting the string before the first comma
Insert ##TempTable(Id)
values(substring(@tempstr,1,patindex('%,%',@tempstr)-1))
--resetting the temporary string to start after the first comma in the previous temp string
select @tempstr = substring(@tempstr,patindex('%,%',@tempstr)+1,Len(@tempstr))
--Checking if there is no more commas
IF patindex('%,%',@tempstr)<=0
Begin
--Inserting the last ID
Insert ##TempTable(Id)
values(@tempstr)
BREAK
End
ELSE
CONTINUE
END
select * from ##TempTable
drop table ##TempTable
Regards,Yelena Varsha
January 23, 2008 at 6:12 pm
djiang (1/23/2008)
Hi Guys,I'm trying to insert a string into a temp table. For instance I want a nvarchar(4000) string
@Ids inserted as separte Ids in a All_id table.
The user can type in 1,2,3,4,5,6 etc separated by commas. How can I get that into my temp table as idividual Ids in a column?
Thanks guys
First things first... you need a "Numbers" or "Tally" table to really make your life easy. All it is is a simple table with a well indexed single column of sequential numbers from 1 to some number. I keep 11,000 numbers in my Tally table because it's larger than VARCHAR(8000) and contains enough numbers for me to generate 30 years worth of dates when I need to. Here's how to make one... add it to your SQL arsenal...
 SELECT TOP 11000 --More than 30 years worth of days if converted to dates
      N = IDENTITY(INT,1,1)
   INTO dbo.Tally
   FROM Master.sys.All_Columns ac1
  CROSS JOIN Master.sys.All_Columns ac2
--===== Add the necessary Clustered PK for blinding speed
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
--===== Allow everyone to use the table
  GRANT SELECT ON dbo.Tally TO PUBLIC
[/font]
DECLARE @lds NVARCHAR(4000)
    SET @lds = N'14,35,9,1234567890,27,12,,12,54,1,34534,45345,234,855,7,,34,53,674,57,56,56,,,,5757567,45745,32'
--===== This just makes sure your temp table doesn't already exist during repetative testing
     IF OBJECT_ID('TempDB..#All_ID','U') IS NOT NULL
        DROP TABLE #All_ID
--===== This just keeps rowcounts from being returned
    SET NOCOUNT ON
--===== This splits the parameter string variable and creates the temp table all in 1 shot.
     -- It's all done using the power of an implicit cross-join with the contents of the @lds variable.
     -- Side benefit is that the "Element" column knows the "position" of each parameter numerically.
 SELECT Element = IDENTITY(INT,1,1),
        ID      = NULLIF(SUBSTRING(','+@lds+',', t.N+1, CHARINDEX(',', ','+@lds+',', t.N+1)-t.N-1),'')
   INTO #All_ID
   FROM dbo.Tally t WITH (NOLOCK)
  WHERE SUBSTRING(','+@lds, t.N, 1) = ',' 
    AND t.N <= LEN(','+@lds) 
--===== This just displays the results... what's in the temp table.
 SELECT * FROM #All_ID[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply