Sql Server 2008 split & Insert to a table

Hi,

I have a variable called @employeeID which is coming as "#" separated & i want to insert them separately into a database table.

create #tempTable
(
empID INT
)

declare  @employeeID VARCHAR(4000)
SET @employeeID = '2001#3001#4001#3211#'

I want to split these ids by '#' & insert them to #tempTable.

Result
---------------
select * from #tempTable

empid
-------
2001
3001
4001
3211

Can i get a sample code for this.
r_pat72Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I have also my version of splitting a string into rows:
http://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html
along with the different usages :)
0
 
lwadwellCommented:
here is one way:
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
	DROP TABLE #tempTable

create table #tempTable (
    empID INT
)

declare  @employeeID VARCHAR(4000)
SET @employeeID = '2001#3001#4001#3211#'

declare  @1emplID  VARCHAR(4000)

WHILE len(@employeeID) > 0
BEGIN
    SET @1emplID = CASE WHEN CHARINDEX('#',@employeeID) > 0 THEN SUBSTRING(@employeeID,1,CHARINDEX('#',@employeeID)-1)
                        ELSE @employeeID
                   END
    INSERT INTO #tempTable ( empID ) values ( @1emplID )
    SET @employeeID = RIGHT(@employeeID,LEN(@employeeID)-LEN(@1emplID)-1)
END

SELECT * FROM #tempTable

Open in new window

0
 
DcpKingCommented:
create table #tempTable
(
empID INT
)

declare  @employeeID VARCHAR(4000)
SET @employeeID = '2001#3001#4001#3211#'
--	-----------------
declare	@intThisPos int, @intNextPos int, @intLength int
set @employeeID = lTrim(rTrim(@employeeID))
set @intLength = len(@employeeID)
set @intThisPos = 1
while 'True' = 'True'
begin
set @intNextPos = charindex('#', @employeeID, @intThisPos)
if @intNextPos = 0 break
insert into #temptable
	select substring(@employeeID, @intThisPos, 4)
set @intThisPos = @intNextPos + 1
end

select * from #tempTable

Open in new window

0
 
ThomasianCommented:
You can use dynamic SQL. Just check for invalid characters to avoid sql injection.
IF PATINDEX('%[^0-9]%',REPLACE(@employeeID,'#','')) = 0 BEGIN
	SET @sql = 'INSERT INTO #tempTable SELECT ' + REPLACE(STUFF(@employeeID,LEN(@employeeID),1,''),'#',' UNION ALL SELECT ')
	exec (@sql)
END

Open in new window

0
 
lwadwellCommented:
To save yourself the trouble ever again ... you could take the logic you choose to use and implement as a custom function and then do:
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
	DROP TABLE #tempTable

create table #tempTable (
    empID INT
)

declare  @employeeID VARCHAR(4000)
SET @employeeID = '2001#3001#4001#3211#'

INSERT INTO #tempTable ( empID ) SELECT items FROM Splitfn(@employeeID,'#')

SELECT * FROM #tempTable

Open in new window

Example function definition:
CREATE FUNCTION Splitfn(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
    if len(@String)<1 or @String is null return       

    while @idx != 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(ltrim(rtrim(@slice)))       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
    return      
end

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.