Updating a column during a BULK INsert that is not part of the BULK Insert

  • I do a BULK insert inside a stored procedure that takes a parameter for the filepath. The file used for the INSERT is .CSV, comma delimited. Each field value in the .csv file is wrapped with "" (double quotes) too. For example, "dog", "cat", "mouse",. During the BULK INSERT the first " is removed. I needed to remove the 2nd " so I added the Cursor code below. All of this works.

    What I now need to do is update a date field in the my_table table that is not a field in the .csv file passed in. It's a date field, the date the row was inserted into my_table.

    I am new to BULK inserts, in fact the below is my first one. How can I set the value of my_table.dateposted from this stored procedure? Is there a way to tell the table.column to auto generate this date value when the row is inserted?

    Thanks

    ALTER PROCEDURE [dbo].[spr_Bulk_Insert_my_table]

    @FilePathAndName varchar(255)

    AS

    BEGIN

    DECLARE @tbl varchar(1000), @col varchar(1000), @sql nvarchar(4000)

    SET NOCOUNT ON;

    SET @sql = 'BULK INSERT dbo.my_table FROM ''' + @FilePathAndName + ''' WITH

    (

    FIRSTROW = 2,

    FIELDTERMINATOR = ''",''

    )'

    EXECUTE sp_executesql @sql

    SET @sql = ''

    DECLARE Cur1 CURSOR FAST_FORWARD FOR

    SELECT o.name tbl, c.name col

    FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.id

    where o.name = 'my_table'

    OPEN Cur1

    FETCH NEXT FROM Cur1 INTO @tbl, @col

    WHILE @@fetch_status = 0

    BEGIN

    SET @sql =

    'UPDATE dbo.' + @tbl + '

    SET ' + QUOTENAME(@col) + ' = RIGHT(LTRIM(RTRIM(' + QUOTENAME(@col) + ')), LEN(LTRIM(RTRIM(' + QUOTENAME(@col) + '))) - 1)

    WHERE LEFT(LTRIM(RTRIM(' + QUOTENAME(@col) + ')), 1) = ''"'' '

    EXECUTE sp_executesql @sql

    FETCH NEXT FROM Cur1 INTO @tbl, @col

    END

    CLOSE Cur1

    DEALLOCATE Cur1

    END

  • bulk insert can handle comma delimited, + quote delimited data automatically, but you must use a format file.

    see this blog for an example:

    http://lanestechblog.blogspot.com/2008/08/sql-server-bulk-insert-using-format.html

    that way there is no need to clean the data, as it comes in perfect on the first pass.

    your command would be something like this:

    bulk INSERT temptable FROM 'csvfilename' WITH (FIRSTROW = 2, FORMATFILE='myformatfilename.txt')

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks Lowell. Any idea how I can set the date field in the SQL Table where the value is not in the .csv file?

  • i typically import into a staging table first, and then into the actual data; that way i can use something like this:

    insert into FinalDestination(column list ....)

    select

    ltrim(rtrim(somefield)),

    coalesce(somedatetimefield2,getdate()),

    coalesce(somemoneyfield,0.00)

    from StagingTable

    that lets me do all the validation and swapping i need to do from staging to final

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • That's what I ended up doing, works perfect, thanks.

  • IF you don't need to validate etc you should use format files to handle this kind of things


    * Noel

Viewing 6 posts - 1 through 5 (of 5 total)

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