Run a SQL set-based INSERT on server side

brendons

My user wants to select a lot of rows (700) from a table and copy/paste them into the same table with a new value for the date field. The multi editor could probably do the job but I haven't tried it as I am concerned about the performance of looping through the selected rows and firing off a create function on every pass through the loop.
If I use a set-based sql statement on the server it gets done in 0.5 seconds so I'm looking for a way to run my set-based sql from the Datatables UI.

Perhaps Datatables already has a function for this?

My thoughts are to build an array of ids and pass this array across to the WHERE statement in sql. Quasi code below:

    buttons: [
                    {extend: "collection",
                        text: "Actions",
                        buttons: [
                            {extend: "selected",
                                text: "Copy",
                                action: function (e, dt, node, config) {
                                    var fL2 = dt.rows({selected: true}).count();
                                        for (var eM2 = 0; eM2 < fL2; eM2++) {                                                            
                                            var copyIds = dt.rows({selected: true}).data()[eM2].sch_ib_classes.class_id.valueOf();
                                            console.log(copyIds);
                                            //build an array of ids
                                        }
                                    $("#modal1").modal('show'); 
                                    //modal form allows user to set a new value for date field
                                    //button on modal passes ids array and new date to sql function
                                }
                            },

The statement to set the copyIds variable looks awful. Perhaps there is a better way to do this?
I need a sanity check please.

allan

concerned about the performance of looping through the selected rows and firing off a create function on every pass through the loop.

Yup - that would be really bad news! However the create() method lets you insert multiple new rows with a single call and you can use the multi-row editing API to modify values as needed.

That won't be as fast as an SQL call, but it will still be fairly fast.

Editor doesn't have a built in method for what you are looking for I'm afraid - the multi-row API is designed for such a use case, or using some custom code to trigger your SQL command.

Allan