locked
post transaction without cursor RRS feed

  • Question

  • I have a staging table that has custnbr,item,pallet and qty. When I load this data to the live table is there a way to do that without a cursor
    and load them by customer and if a customer record fails write that to a log but keep processing the reminder of that customer and remaining records for other customers.

     I need to make sure the live table is loaded but send me email of the reocords that failed to load.


     Thanks.
    Tuesday, February 2, 2010 1:50 AM

Answers

  • Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

    Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

    >> I have a staging table that has cust_nbr, item_nbr, pallet_nbr and pallet_qty. <<

     

    How nice for you! Do you want ot shared with the rest of us? Or just keep being rude? I guessed at the data element names and their meaning. For example, I assumed based on nothing you told us that quantity is at the pallet level.

     

    >> When I load this data to the live table, is there a way to do that without a cursor and load them by customer .. <<

    The short answer is: Yes, again based on nothing you told us, you write an “INSERT INTO .. SELECT.. FROM <stage table> WHERE <data validation> ;”or you use an ETL tool.

    >> and if a customer record [sic: rows are not records] fails write that to a log but keep processing the reminder of that customer and remaining records [sic] for other customers. <<

    This makes no sense from an SQL viewpoint. A set is inserted or not as a set. There is no record-at-at-a-time processing. Now, if you are using an ETL tool that works with mag tape files, that might make sense. This is all front processing.

    >> I need to make sure the live table is loaded but send me email of the records [sic] that failed to load. <<

    That is a SELECT on the staging table. If these are records, a procedural language procedure then where is your ETL tool? This is not a real SQL question. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    • Marked as answer by Kalman Toth Saturday, August 20, 2011 6:34 PM
    Tuesday, August 9, 2011 7:06 PM
  • if you want to process a record at a time then you need some form of loop " keep processing the reminder of that customer " this inply a loop.

    in t-sql you would use a cursor

    however

    you may be asking for a parallel update in which " keep processing the reminder of that customer " is a misleading expression

     

     

    I recommend SSIS  as it looks like is a tool for what you are tring to do.

     


    Alun Davies
    • Marked as answer by Kalman Toth Saturday, August 20, 2011 6:34 PM
    Tuesday, August 9, 2011 3:09 PM

All replies

  • if you want to process a record at a time then you need some form of loop " keep processing the reminder of that customer " this inply a loop.

    in t-sql you would use a cursor

    however

    you may be asking for a parallel update in which " keep processing the reminder of that customer " is a misleading expression

     

     

    I recommend SSIS  as it looks like is a tool for what you are tring to do.

     


    Alun Davies
    • Marked as answer by Kalman Toth Saturday, August 20, 2011 6:34 PM
    Tuesday, August 9, 2011 3:09 PM
  • Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

    Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

    >> I have a staging table that has cust_nbr, item_nbr, pallet_nbr and pallet_qty. <<

     

    How nice for you! Do you want ot shared with the rest of us? Or just keep being rude? I guessed at the data element names and their meaning. For example, I assumed based on nothing you told us that quantity is at the pallet level.

     

    >> When I load this data to the live table, is there a way to do that without a cursor and load them by customer .. <<

    The short answer is: Yes, again based on nothing you told us, you write an “INSERT INTO .. SELECT.. FROM <stage table> WHERE <data validation> ;”or you use an ETL tool.

    >> and if a customer record [sic: rows are not records] fails write that to a log but keep processing the reminder of that customer and remaining records [sic] for other customers. <<

    This makes no sense from an SQL viewpoint. A set is inserted or not as a set. There is no record-at-at-a-time processing. Now, if you are using an ETL tool that works with mag tape files, that might make sense. This is all front processing.

    >> I need to make sure the live table is loaded but send me email of the records [sic] that failed to load. <<

    That is a SELECT on the staging table. If these are records, a procedural language procedure then where is your ETL tool? This is not a real SQL question. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    • Marked as answer by Kalman Toth Saturday, August 20, 2011 6:34 PM
    Tuesday, August 9, 2011 7:06 PM