To insert table of data into database through SP

  • 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 !

  • 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

  • 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

  • Hi,

    Thank you

  • 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!

  • 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)

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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