I have on table PersonDetail which contains NAME and AGE as column. I am using one application which is taking all data as String by comma and pipe separated value like 'Acton,58|Nairi,20|Sara,14|Denny,52' (Format is same as given)
I want to insert that data into table(PersonDetail) but I don't know how can I separate it as a NAME and AGE.
Some one suggested me to create function which can separate the data, But i have no idea to do it. Can any one give me suggestion?? Thanks in advance :)
you can create Multi-statement table value function to separate the data. you just need to insert each NAME and AGE into table type variable and after inserting you should return that table as given below.
CREATE FUNCTION UDF_InsertDataFromString
(
@dataString VARCHAR(5000)
)
RETURNS @insertedData TABLE
(
NAME VARCHAR(30),
AGE INT
)
AS
BEGIN
DECLARE @pipeIndex INT,
@commaIndex INT,
@LENGTH INT,
@NAME VARCHAR(100),
@AGE INT
SELECT @LENGTH = LEN(RTRIM(LTRIM(@dataString))),
@dataString = RTRIM(LTRIM(@dataString))
WHILE (@LENGTH <> 0)
BEGIN
SELECT @LENGTH = LEN(@dataString),
@commaIndex = CHARINDEX(',', @dataString),
@pipeIndex = CHARINDEX('|', @dataString)
IF(@pipeIndex = 0) SET @pipeIndex = @LENGTH +1
SELECT @NAME = RTRIM(LTRIM(SUBSTRING(@dataString, 1, @commaIndex-1))),
@AGE = RTRIM(LTRIM(SUBSTRING(@dataString, @commaIndex+1, @pipeIndex-@commaIndex-1))),
@dataString = RTRIM(LTRIM(SUBSTRING(@dataString, @pipeIndex+1, @LENGTH-@commaIndex-1)))
INSERT INTO @insertedData(NAME, AGE)
VALUES(@NAME, @AGE)
SELECT @LENGTH = LEN(@dataString)
END
RETURN
END
Now you can use this function while inserting data from string, you just need to pass string as parameter in function as given below.
DECLARE @personDetail TABLE(NAME VARCHAR(30), AGE INT)
INSERT INTO @personDetail(NAME, AGE)
SELECT NAME, AGE
FROM dbo.UDF_InsertDataFromString('Acton,58|Nairi,20|Sara,14|Denny,52')
SELECT NAME, AGE
FROM @personDetail
use this split func
CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
and then use this query
DECLARE @x table ( id int identity(1,1)
, str varchar(50) )
DECLARE @str varchar(50)='Acton,58|Nairi,20|Sara,14|Denny,52'
INSERT INTO @x
SELECT *
FROM [dbo].[fnSplitString] (@str ,'|')
SELECT *
from @x
DECLARE @y int=(SELECT count(*)
FROM @x)
DECLARE @e varchar(50)
DECLARE @b varchar(50)
DECLARE @c varchar(50)
WHILE @y!=0
BEGIN
set @e =(SELECT str
FROM @x
where id=@y)
set @b =(substring(@e,1,(charindex(',',@e)-1)))
set @c = (substring(@e,(charindex(',',@e)+1),len(@e)-charindex(',',@e)))
INSERT INTO PersonDetail
SELECT distinct @b
, @c
FROM @x
SET @y=@y-1
END
yes you can create your own function.
just you need to apply different string function for that
bulk import.