USE FencoDW
GO
DECLARE @ID INT, @PR varchar(50)
DECLARE @getID CURSOR
SET @getID = CURSOR FOR
SELECT [Load Number], [Primary Reference(s)]
FROM Accounting.TransplaceLoadInfo
OPEN @getID
FETCH NEXT
FROM @getID INTO @ID, @PR
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @ID
PRINT @PR
INSERT INTO dbo.T1 ([Load Number], [Primary Reference(s)])
SELECT @ID, [Val] from dbo.ParseCSV(@PR, ',')
FETCH NEXT
FROM @getID INTO @ID, @PR
END
CLOSE @getID
DEALLOCATE @getID
GO
to parse a cvs string and return a table with “Val” as the column name.
=====
ALTER FUNCTION [dbo].[ParseCSV] (
@param varchar(8000),
@Delimeter varchar(10)
)
RETURNS @tblRes TABLE
(
-- Columns returned by the function
Val varchar(100)
)
AS begin
DECLARE @Val varchar(100)
DECLARE @StartPos int, @Length int
WHILE LEN(@param) > 0
BEGIN
SET @StartPos = CHARINDEX(@Delimeter, @param)
IF @StartPos < 0 SET @StartPos = 0
SET @Length = LEN(@param) - @StartPos - 1
IF @Length < 0 SET @Length = 0
IF @StartPos > 0
BEGIN
SET @Val = SUBSTRING(@param, 1, @StartPos - 1)
SET @param = SUBSTRING(@param, @StartPos + 1, LEN(@param) - @StartPos)
END
ELSE
BEGIN
set @param = ltrim(rtrim(@param))
SET @Val = @param
SET @param = ''
END
set @Val = Ltrim(Rtrim(@Val))
INSERT @tblRes (Val) VALUES(@Val)
END
--Show all records in the @tblRes table
--SELECT * FROM @tblRes
RETURN
end