Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I would separate the import process from any triggers. A trigger is useful if you're going to have rows being constantly added to the import table from a constantly running, outside source. It doesn't sound like this is your situation though, since you'll be importing an entire file at once. Triggers tend to hide code and can be difficult to work with in some situations.</p> <p>How often are you importing these files?</p> <p>I would have an import process that is mostly stand-alone. It might use stored procedures or tables in the database, but I wouldn't use triggers. A simple approach would be something like below. I've added a column to the <code>Legacy_Invoices</code> (also renamed to something that's more descriptive) so that you can track when items have been imported and from where. You can expand this to track more information if necessary.</p> <p>Also, I don't see how you're tracking invoice numbers in your code. I've assumed an IDENTITY column in the <code>Legacy_Invoices</code>. This is almost certainly insufficient since I assume that you're creating invoices in your own system as well (outside of the legacy system). Without knowing your invoice numbering scheme though, it's impossible to give a solution there.</p> <pre><code>BEGIN TRAN DECLARE @now DATETIME = GETDATE() UPDATE Legacy_Invoices SET import_datetime = @now WHERE import_status = 'Awaiting Import' INSERT INTO dbo.Invoices (invoice_no, cust_no) SELECT DISTINCT invoice_no, cust_no FROM Legacy_Invoices WHERE import_datetime = @now UPDATE Legacy_Invoices SET import_status = 'Invoice Imported' WHERE import_datetime = @now INSERT INTO dbo.Invoice_Lines (invoice_no, item_no, item_qty, item_prc) SELECT invoice_no, item_no_1, item_qty_1, item_prc_1 FROM Legacy_Invoices LI WHERE import_datetime = @now AND import_status = 'Invoice Imported' AND item_no_1 IS NOT NULL UPDATE Legacy_Invoices SET import_status = 'Item 1 Imported' WHERE import_datetime = @now AND import_status = 'Invoice Imported' &lt;Repeat for item_no_2 through 7&gt; COMMIT TRAN </code></pre> <p>Here's a <strong>big</strong> caveat though. While cursors are normally not desirable in SQL and you want to use set-based processing versus RBAR (row by agonizing row) processing, data imports are often an exception.</p> <p>The problem with the above is that if one row fails, that whole import step fails. Also, it's very difficult to run a single entity (invoice plus line items) through business logic when you're importing them in bulk. This is one place where SSIS really shines. It's extremely fast (assuming that you set it up properly), even when importing one entity at a time. You can then put all sorts of error-handling in it to make sure that the import runs smoothly. One import row has an erroneous invoice number? No problem, mark it as an error and move on. A row has item# 2 filled in, but no item#1 or has a price without a quantity? No problem, mark the error and move on.</p> <p>For a single import I might stick with the code above (adding in appropriate error handling of course), but for a repeating process I would almost certainly use SSIS. You can import millions of rows in seconds or minutes even with individual error handling on each business entity.</p> <p>If you have any problems with getting SSIS running (there are tutorials all over the web and on MSDN at Microsoft) then post any problems here and you should get quick answers.</p>
    singulars
    1. This table or related slice is empty.
    1. POData Import Question: Should I use a cursor?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USTom H
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POData Import Question: Should I use a cursor?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload