October 29, 2008 at 4:48 am
Hi,
I need to insert a delimited string of data into database through a stored procedure
The string is as below:
[dbo].[insertTravel11] 1440,'TR1',5000,'ghgh','$1099,14,44,20,$188,14,44,30,$400,14,44,80,$','$',','
each ' $ 'represents row and ' ,' rep column.
my table is slave_tab(invoiceno varchar(20),trno int,partyname varchar(15),amount1 int)
I wrote sp where i am spliting the string into rows and columns.....
and the pblm is I cant get the corect position of the delimiters using CHARINDEX () .....please help me to find out any other alternatives..............
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[insertTravel11]
@trno int,
@trtype varchar(15),
@amount float,
@head varchar(35),
@sInputList VARCHAR(8000),
@Delimiter1 VARCHAR(8000),
@sDelimiter VARCHAR(8000)
AS
--SET @Delimiter1 ='$'
-- SET @sDelimiter =','
BEGIN
DECLARE @Items VARCHAR(8000),@sItem int,@sItem1 VARCHAR(15),@sItem2 float,@sItem3 varchar(35),@spot smallint,@spot1 smallint,@currentpos int,@nextposition int,@CPOS int,@LenDel int
DECLARE @List TABLE (sItem VARCHAR(15),sItem1 int,sItem2 VARCHAR(35),sItem3 INT)
WHILE @sInputList <> ''
BEGIN
--SET @spot = CHARINDEX(@Delimiter1,@sInputList)
--
--IF @spot > 0
WHILE CHARINDEX(@Delimiter1,@sInputList,0) <> 0
BEGIN
SET @LenDel = LEN(@Delimiter1 + '$') - 1
SET @CPOS = CHARINDEX(@Delimiter1,@sInputList)
SELECT
@Items =RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter1,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter1,@sInputList,0)+LEN(@Delimiter1),LEN(@sInputList))))
--SET @spot1 =CHARINDEX(@sDelimiter,@Items)
--IF @spot1 > 0
WHILE CHARINDEX(@sDelimiter,@Items,0) <> 0
BEGIN
SET @currentpos = CHARINDEX(@sDelimiter,@Items)
SET @nextposition =1
SELECT
@sItem=(LTRIM(RTRIM(SUBSTRING(@Items, @nextposition,@currentpos-1)))),
@Items=LTRIM(RTRIM(SUBSTRING(@Items,CHARINDEX(@sDelimiter, @Items,@currentpos)+LEN(@sDelimiter),LEN(@Items))))
SET @currentpos = CHARINDEX(@sDelimiter,@Items)
SET @nextposition = @currentpos+1
SELECT
@sItem1=CAST(LTRIM(RTRIM(SUBSTRING( @Items,@nextposition,@currentpos -1))) AS INT),
@Items=LTRIM(RTRIM(SUBSTRING(@Items,CHARINDEX(@sDelimiter, @Items,@currentpos)+LEN(@sDelimiter),LEN(@Items))))
SET @currentpos = CHARINDEX(@sDelimiter,@Items)
SET @nextposition = @currentpos+1
SELECT
@sItem2=(LTRIM(RTRIM(SUBSTRING( @Items,@nextposition,@currentpos -1)))),
@Items=LTRIM(RTRIM(SUBSTRING(@Items,CHARINDEX(@sDelimiter, @Items,@currentpos)+LEN(@sDelimiter),LEN(@Items))))
SET @currentpos = CHARINDEX(@sDelimiter,@Items)
SET @nextposition = @currentpos+1
SELECT
@sItem3=CAST(LTRIM(RTRIM(SUBSTRING( @Items,@nextposition,@currentpos -1))) AS float),
@Items=LTRIM(RTRIM(SUBSTRING(@Items,CHARINDEX(@sDelimiter, @Items,@currentpos)+LEN(@sDelimiter),LEN(@Items))))
-- c1 =c1+1
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem,@sItem1,@sItem2,@sItem3
insert into slave_tab(invoiceno,trno,partyname,amount1)values
(
@sItem,
@sItem1,
@sItem2,
@sItem3
)
END
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sItem,@sItem1,@sItem2,@sItem
SELECT * FROM @List
--SET @sInputList = STUFF(@sInputList, 1, @CPOS+@LenDel-1, '')
--SET @CPOS =@CPOS+@LenDel -1
END
insert INTO master_tab(trno,trtype,amount,head)values
(
@trno,
@trtype,
@amount,
@head
)
END
Thanks !
October 29, 2008 at 5:17 am
Of course your table desc and the actual procedure helps, but for testing and understanding purposes you really should put some sample data into a post, using the guidelines in following post:
http://www.sqlservercentral.com/articles/Best+Practices/61537/.
Ronald
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
October 29, 2008 at 5:55 am
That's an awful lot of work for something you can get for free if you do one of two things. You can save your delimited string to a file and use the BULK INSERT command which can parse custom row and column delimiters extremely well. Or, you could place the string into XML and use XPath queries to pluck the data out. Either way will be a lot easier than what you're trying to do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2008 at 8:59 pm
Hi,
Thank you
October 29, 2008 at 11:47 pm
I have a BULK INSERT command as below
BULK INSERT slave_tab
FROM 'C:\Documents and Settings\INTEL1\Desktop\exampledata.txt'
WITH
(
FIELDTERMINATOR ='^',
ROWTERMINATOR ='^$'
)
its working fine...
I need to pass the file 'C:\Documents and Settings\INTEL1\Desktop\exampledata.txt' as the parameter of an Stored Procedure...how can I do it...
exampledata.txt' contains a string of data as below...
677^5678^hhh^44^$5566^5678^hgg^33^$777^5678^fg^55^$666^5678^ff^333^$
Thanks!
October 31, 2008 at 7:28 am
Assuming that you have a parameter in your stored procedure to receive the filename, something like what follows:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.spWHATEVER_YOU_CALL_IT (
@FILE_NAME varchar(255)
)
AS
BEGIN
BULK INSERT slave_tab
FROM @FILE_NAME
WITH
(
FIELDTERMINATOR ='^',
ROWTERMINATOR ='^$'
)
END
GO
Then you can call that procedure as follows:
EXEC dbo.spWHATEVER_YOU_CALL_IT 'C:\Documents and Settings\INTEL1\Desktop\exampledata.txt'
Parameters to an sp are placed after the procedure name, and if more than one, are comma separated. Single quotes are used to enclose character strings.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 3, 2008 at 8:47 am
A reply from Jay Nichols (he's having posting issues):
Create a function like this and your data will be parsed into a table which you can then use to update data in a table:
/****** Object: UserDefinedFunction [dbo].[ParseLine] Script Date: 11/03/2008 08:14:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE FUNCTION [dbo].[ParseLine]
(
@RepParam nvarchar(4000), @Delim char(1)= ','
)
RETURNS @Values TABLE (Param nvarchar(4000))
AS
BEGIN
DECLARE @chrind INT
DECLARE @Piece nvarchar(10)
SELECT @chrind = 1
WHILE @chrind > 0
BEGIN
SELECT @chrind = CHARINDEX(@Delim,@RepParam)
IF @chrind > 0
SELECT @Piece = LEFT(@RepParam,@chrind - 1)
ELSE
SELECT @Piece = @RepParam
INSERT @Values(Param) VALUES(Cast(@Piece AS INT))
SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
IF LEN(@RepParam) = 0 BREAK
END
RETURN
END
Jay Nichols
Data Base Administrator
November 3, 2008 at 9:46 pm
As someone else said in an earlier post...
M u s t... r e s i s t... t e m p t a t i o n... GAH!!!
Please see the following article on how to do very high speed splits on 1, 2, and 3 dimensional parameters...
http://www.sqlservercentral.com/articles/T-SQL/63003/
... none of which involve the use of a While loop. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2008 at 1:43 am
Hi ,
I got the output..thanks a lot for ur reply!
Thanks!
Meera
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply