Output to file and then Bulk Insert problems/questions

  • There is a column in a table which contains pipe delimited values.  I want to manipulate these values individually, so I thought I would create a temp or work table to store the values and use a series of TSQL statements to extract the column to a text file then insert the values to this work table.  Make sense?

    I am able to do as I describe except that I have to manually edit the output file before I can bulk insert. 

    When I use the Query Analyzer to create the file, I'm stuck with text like "(4 rows affected)" at the bottom.  This annoys the bulk insert--even if I tell it first row/last row.  I've tried various switches on the command line using osql but I cannot supress that message.

    So I ask how do you supress that "# rows affected" message? Also, I seem to end up with an empty first row, so I have to tell bulk insert FIRSTROW=2--can that be avoided?

    Also, I may want to do this from a stored procedure--or even to do it in the QA--is there TSQL to output to a file from within a stored proc or the QA?

    Thanks for any help.

    Gina

  • To suppress the # rows affected message just include this before your query.

    SET NOCOUNT ON

    This will take care of the trailing message from the query results.

  • A better way would be to use a function to separate the text column by the delimeter so that you can avoid exporting directly.

    CREATE FUNCTION dbo.FAQ_ListToSingleColumn

    (

        @cslist VARCHAR(8000)

    , @delim char(1)

    , @offset smallint

    )

    RETURNS varchar (255)

    BEGIN 

     

        DECLARE @spot SMALLINT, @STR VARCHAR(8000), @sql VARCHAR(8000), @count int, @returnstr varchar(255)

     set @count = 0    

        WHILE @cslist <> '' 

        BEGIN 

     set @count = @count + 1

            SET @spot = CHARINDEX(@delim, @cslist) 

            IF @spot>0 

                BEGIN 

                    SET @STR = LEFT(@cslist, @spot-1) 

                    SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot) 

                END 

            ELSE 

                BEGIN 

                    SET @STR = @cslist 

                    SET @cslist = '' 

                END 

            if @count = @offset begin

      set @returnstr = @STR

      break

     end

        END 

        RETURN (@returnstr)

    END

    GO

     

    select dbo.FAQ_ListToSingleColumn(@t,'|',3) will return the 3rd element of the string.  You probably know how many different columns you will end up with so you could create statement to select all the columns:

    select dbo.FAQ_ListToSingleColumn(@t,'|',1)  as COL1

    , dbo.FAQ_ListToSingleColumn(@t,'|',2)  as COL2

    , dbo.FAQ_ListToSingleColumn(@t,'|',3)  as COL3

    , dbo.FAQ_ListToSingleColumn(@t,'|',4)  as COL4

    ...

    from <table>

     

    This would be a lot simpler and avoid problems of blank rows etc.  I have not tested the function exhaustively so it may have a bug in it.

    J

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply