CODEWITHSUNDEEP
phparrayssql-server
FreshPro
FreshPro

Reputation: 873

Multiple insert to sql using an array of insert scripts

Because I don't want to do 100 insert script, I am trying to create an insert array. And after loop is finished, insert with only 1 query. (I'm using php) I am looping through some DB operations, doing some calculations etc. and generate insert scripts. Output of this array is like this:

$this->insert_array

[0] -> INSERT INTO tbl_electricty (fld_value, fld_active) VALUES ('1', true)
[1] -> INSERT INTO tbl_electricty (fld_value, fld_active) VALUES ('2', false)
[2] -> INSERT INTO tbl_electricty (fld_value, fld_active, fld_model) VALUES ('1', false, 'Powys')
....
....
[99] -> INSERT INTO tbl_electricty (fld_value, fld_active) VALUES ('7521', true)

How to merge this elements into 1 big insert script?

Upvotes: 0

Views: 41

Answers (2)

Tanner
Tanner

Reputation: 22753

You could use your array to populate a temporary table then insert the results of your temporary table to the destination once the table is ready. A sample (purely SQL) would look like this:

CREATE TABLE #temp (value INT, active BIT, model NVARCHAR(10));

INSERT INTO #temp( value, active, model )
VALUES
(1, 1, ''),
(2, 0, ''),
(1, 0, 'Powys');

CREATE TABLE #destination (value INT, active BIT, model NVARCHAR(10));

INSERT INTO #destination( value, active, model )
SELECT t.value, t.active, t.model
FROM #temp AS t;

SELECT *
FROM #destination;

DROP TABLE #temp;
DROP TABLE #destination;

Upvotes: 2

K.B
K.B

Reputation: 885

Instead of creating it as array, create it as following

INSERT INTO tbl_electricty (fld_value, fld_active) VALUES ('7521', true),('7522', true),('7523', true),('7524', true)............,('7621', true);

then run the query.

Upvotes: 0

Related Questions