Post reply

Bulk Insert

  • August 27, 2009 at 12:00 pm

    #138507

    Hi Guys

    I need to do an insert from a txt file into a table in sql server 2005.

    However the table consists of 6 columns but my txt file has only 5 as I have to specify the other value in the stored procedure.

    How can I go about doing this?

    Is there a better way to import a txt file to table other then BULK insert?

    Thanks

  • August 27, 2009 at 3:15 pm

    #1045922

    Import into a staging table and then insert from you staging table into your final destination.

    You'll need to specify the columns in you insert from staging to destination, see BOL for insert syntax.

    I might use SSIS rather than bulk. Is this a one-time or ongoing?

  • August 29, 2009 at 11:57 am

    #1046514

    Hey

    Thanks for replying.

    This is going to be a one off thing.

    Idealy I would like to do it without using another table. Is this possible?

    The text file has 4 values in with the table containing 5. I need to specify the 5th value myself as it needs to be the current date.

    Thanks

  • August 29, 2009 at 6:13 pm

    #1046557

    Set a default column value on the table for that column to be equal to GETDATE().

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • September 1, 2009 at 2:47 am

    #1047358

    OK guys Ive set the column date as a default value of the current date so that problem is solved.

    However in the file I have there are 2 rows of data that need to be inserted but then a number underneath which displays how many rows there are in the textfile.

    This is causing a problem for my table as it thinks its a row to be inserted and falls over.

    How can I specify that the number at the end of the file does not need to be inserted?

    thanks

  • September 1, 2009 at 6:39 am

    #1047468

    Can you show us an example of what you mean? Plus the BULK INSERT command that you are currently using? thnx...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • September 2, 2009 at 9:46 am

    #1048258

    I'm just guessing, but your BULK INSERT statement looks something like this - -

    BULK INSERT

    FROM 'filelocation\filename.txt'

    WITH (FIELDTERMINATOR = ',') --if commas separate your values--

    You said you have two rows of data followed by a row indicating how many rows are in the import file. So make your WITH statement look like this . .

    WITH (FIELDTERMINATOR = ',',

    LASTROW = 2)

    . . and any rows after row two will be ignored.

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

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