Databases
StackOverflow2013
Postsdbo
POData Import Question: Should I use a cursor?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POData Import Question: Should I use a cursor?
    primarykey
    data
    text
    <p>I'm currently working on a SQL import routine to import data from a legacy application to a more modern robust system. The routine simply imports data from a flat-file legacy table (stored as a .csv file) into SQL Server that follows the classic order/order-detail pattern. Here's what both tables look like:</p> <pre><code>**LEGACY_TABLE** Cust_No Item_1_No Item_1_Qty Item_1_Prc Item_2_No Item_2_Qty Item_2_Prc ... Item_7_No Item_7_Qty Item_7_Prc </code></pre> <p>As you can see, the legacy table is basically a 22 column spreadsheet that is used to represent a customer, along with up to 7 items and their quantity and purchase price, respectively. </p> <p>The new table(s) look like this: </p> <pre><code>**INVOICE** Invoice_No Cust_No **INVOICE_LINE_ITEM** Invoice_No Item_No Item_Qty Item_Prc </code></pre> <p>My quick-and-dirty approach has been to create a replica of the LEGACY_TABLE (let's call it LEGACY_TABLE_SQL) in SQL Server. This table will be populated from the .csv file using a database import that is already built into the application.<br> From there, I created a stored procedure to actually copy each of the values in the LEGACY_TABLE_SQL table to the INVOICE/INVOICE_LINE_ITEM tables as well as handle the underlying logical constraints (i.e. performing existence tests, checking for already open invoices, etc.). Finally, I've created a database trigger that calls the stored procedure when new data is inserted into the LEGACY_TABLE_SQL table. </p> <p>The stored procedure looks something like this: </p> <pre><code>CREATE PROC IMPORT_PROCEDURE @CUST_NO @ITEM_NO @ITEM_QTY @ITEM_PRC </code></pre> <p>However, instead of calling the procedure once, I actually call the stored procedure seven times (once for each item) using a database trigger. I only execute the stored procedure when the ITEM_NO is NOT NULL, to account for blank items in the .csv file. Therefore, my trigger looks like this:</p> <pre><code>CREATE TRIGGER IMPORT_TRIGGER if ITEM_NO_1 IS NOT NULL begin exec IMPORT_PROCEDURE (CUST_NO,ITEM_NO_1, ITEM_QTY_1, ITEM_PRC_1) end </code></pre> <p>...so on and so forth. </p> <p>I'm not sure that this is the most efficient way to accomplish this task. Does anyone have any tips or insight that they wouldn't mind sharing?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USTelJanini
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POData Import Question: Should I use a cursor?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCursor is not efficient. For each request, there will be a round trip.
      singulars
      1. POData Import Question: Should I use a cursor?
      1. USzsong
    2. COSo you're saying the approach I'm using is correct?
      singulars
      1. POData Import Question: Should I use a cursor?
      1. USTelJanini
    3. COLooks perfect - definitely don't use a cursor for this
      singulars
      1. POData Import Question: Should I use a cursor?
      1. USDerek Kromm
 

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