Professional Web Applications Themes

How do I error check with multiple BULK INSERTs - Microsoft SQL / MS SQL Server

I have several tables being loaded at a time by text files using the BULK INSERT command...How can I do this properly to ensure that duplicate data that is not being loaded on this execution does not prevent non-duplicate data from each file to continue to load? Some of the data in each file, may be duplicate, but not all of it. This is what I have so far on the insert commands: Try ' Insert code to process Mink data. conn.Open() 'this for TBL_Catalog sqlstr = Nothing sqlstr = "BULK INSERT [Mink].[dbo].[TBL_Catalog]" + _ " FROM '\Server05\c\Catalog1.txt' WITH (FIELDTERMINATOR ...

  1. #1

    Default How do I error check with multiple BULK INSERTs

    I have several tables being loaded at a time by text files using the
    BULK INSERT command...How can I do this properly to ensure that
    duplicate data that is not being loaded on this execution does not
    prevent non-duplicate data from each file to continue to load? Some of
    the data in each file, may be duplicate, but not all of it.
    This is what I have so far on the insert commands:
    Try
    ' Insert code to process Mink data.
    conn.Open()

    'this for TBL_Catalog
    sqlstr = Nothing
    sqlstr = "BULK INSERT [Mink].[dbo].[TBL_Catalog]" + _
    " FROM '\\Server05\c\Catalog1.txt' WITH (FIELDTERMINATOR
    = '\t')"
    sqlInsertCommand2.ExecuteNonQuery()
    'this for TBL_Group
    sqlstr = Nothing
    sqlstr = "BULK INSERT [Mink].[dbo].[TBL_Group]" + _
    " FROM '\\Server05\c\BatchGroup1.txt' WITH
    (FIELDTERMINATOR = '\t')"
    sqlInsertCommand2.ExecuteNonQuery()
    'this for TBL_ItemsSold
    sqlstr = Nothing
    sqlstr = "BULK INSERT [Mink].[dbo].[TBL_ItemsSold]" + _
    " FROM '\\Server05\c\ItemSold1.txt' WITH
    (FIELDTERMINATOR = '\t')"
    sqlInsertCommand2.ExecuteNonQuery()
    'this for TBL_MasterItem
    sqlstr = Nothing
    sqlstr = "BULK INSERT [Mink].[dbo].[TBL_MasterItem]" + _
    " FROM '\\Server05\c\Item1.txt' WITH (FIELDTERMINATOR =
    '\t')"
    sqlInsertCommand2.ExecuteNonQuery()
    'this for TBL_Seller
    sqlstr = Nothing
    sqlstr = "BULK INSERT [Mink].[dbo].[TBL_Seller]" + _
    " FROM '\\Server05\c\Seller1.txt' WITH (FIELDTERMINATOR
    = '\t')"
    sqlInsertCommand2.ExecuteNonQuery()
    Me.txtFileText.Text = ("Transaction-SQL Successful!")
    Catch ex As Exception
    Dim strMessage As String
    strMessage = ex.Message
    Me.txtFileText.Text = (strMessage)
    Finally
    conn.Close()
    End Try
    Thanks for any help!
    Trint
    .Net programmer
    com

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Trint Guest

  2. #2

    Default Re: How do I error check with multiple BULK INSERTs

    Trint,

    The texbook way to load this kind of data which needs to be cleansed, is to
    load it into a staging table first and then perform your integrity checks
    and validations before or while moving the data to the production table.

    Hope this helps,

    Bill

    "Trint Smith" <com> wrote in message
    news:phx.gbl... 


    Alter Guest

  3. #3

    Default Re: How do I error check with multiple BULK INSERTs

    Bill,
    Thanks for the reply...Do you have any information on how to do that?
    I've been also considering MAXERRORS...is that something I should try?
    Thanks,
    Trint

    ..Net programmer
    com

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Trint Guest

Similar Threads

  1. Bulk inserts with update
    By user in forum MySQL
    Replies: 2
    Last Post: January 2nd, 04:06 PM
  2. Multiple Inserts based on check box?
    By mikeap in forum Coldfusion Database Access
    Replies: 3
    Last Post: August 8th, 05:33 PM
  3. Can I have a form that inserts multiple data
    By Chris in forum Microsoft Access
    Replies: 2
    Last Post: July 2nd, 11:25 PM
  4. Memory usage of Bulk Inserts
    By Connor McDonald in forum Oracle Server
    Replies: 0
    Last Post: December 5th, 08:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139