locked
bulk insert records from one entity to another by date range RRS feed

  • Question

  • Exactly as stated above, I want to query and entity for all records by a date range and insert them into another entity.

    After they have been inserted into the entity, I want to delete the originals, how can I accomplish this?

    Stored Procedure?

    Anyone have a sample code for this?

    Thursday, April 11, 2013 8:20 PM

Answers

  • I can (and probably others) can help you out here.  I don't have much time (bed time), so let me get you started.

    One of the best videos I found to really help to start automating things is this one:

    20 - How Do I: Copy Data from One Row into a New Row?

    If you use the principles shown in these, except going from one table to the other, you could do what I think you are trying to accomplish, all in LightSwitch.  It might not be that fast, but you get max control.

    Now if you wanted to do this in a Trigger, and you know how to get at the database you use, you can create a trigger that does your work.  Add it to the insert or Update, or both.

    If you haven't used triggers, ask my friend Google.  You will find things like this:

    Example of SQL Triggers.

    Or like has been suggested, the command Table Pattern is a simple way to run SQL code.

    By all means, if you get stuck, post it here, these guys are good.


    Would someone please turn on the LIGHT?

    • Marked as answer by zack142 Wednesday, April 17, 2013 12:53 AM
    Friday, April 12, 2013 3:31 AM

All replies

  • Zach,

    Hey, Here is some conversations about how to do this... see my posts especially about using SQL Server's Begin Try... End Try / Begin Catch... End Catch using a table field to return the error.

    How to execute SQL command inside Save Pipeline?

    If you have any questions about how to get that set up, let me know.


    Would someone please turn on the LIGHT?

    Thursday, April 11, 2013 9:36 PM
  • Thanks Joe, again. 

    I am fairly new to Lightswitch, so I was hoping for a working example. I have a table with customer, date and dollar amount. I want to load up a grid with the a date range select, press a button and move them to another table and delete them. I know how to create the grid, button and load the grid with the correct records. what i need is a can execute to accomplish this. Can you help me?

    Thanks!

    Friday, April 12, 2013 12:15 AM
  • I would recommend doing this by using Command Table Pattern.  Paul's blog post explains this, and you can use his "quick and dirty" method for what you are doing.  I don't have an example handy but I use this all the time.  Essentially, you would insert a record into a "command table".  The command table's Inserting() method would then fire your query and you would use a For Each statement to insert and delete within the query results.  If this doesn't help I will post an example, but there are a few steps involved in the set up.  LS does this sort of thing quite well I assure you.
    • Edited by Hessc Friday, April 12, 2013 3:27 AM
    Friday, April 12, 2013 3:24 AM
  • I can (and probably others) can help you out here.  I don't have much time (bed time), so let me get you started.

    One of the best videos I found to really help to start automating things is this one:

    20 - How Do I: Copy Data from One Row into a New Row?

    If you use the principles shown in these, except going from one table to the other, you could do what I think you are trying to accomplish, all in LightSwitch.  It might not be that fast, but you get max control.

    Now if you wanted to do this in a Trigger, and you know how to get at the database you use, you can create a trigger that does your work.  Add it to the insert or Update, or both.

    If you haven't used triggers, ask my friend Google.  You will find things like this:

    Example of SQL Triggers.

    Or like has been suggested, the command Table Pattern is a simple way to run SQL code.

    By all means, if you get stuck, post it here, these guys are good.


    Would someone please turn on the LIGHT?

    • Marked as answer by zack142 Wednesday, April 17, 2013 12:53 AM
    Friday, April 12, 2013 3:31 AM