xBasic Insert Multiple Rows One Statement

[ssmoore1]

Hi Everyone,

Sorry if this is an obvious answer but I have poked around and can't seem to find it.

In xBasic how you do you insert/update multiple rows with one SQL statement, so you don't have to make multiple calls to the database.

So lets say I am looping at an array with 100 records, how can I insert or update those records with one SQL statement instead of having to insert the records 100 times or update them?

Thanks in advance.

Scott

[jgrannis]

Re: xBasic Insert Multiple Rows One Statement

This may be more of a SQL syntax question depending on your database. Most all databases have their flavor of statements that "insert or update", "insert or replace" or "insert from select".
What database are the inserts or updates going into? As you loop through, for each row, do you know beforehand which records need to be inserted and which only need to be updated?

[ssmoore1]

Re: xBasic Insert Multiple Rows One Statement

Hi,

This would be for MySQL.

Also, this would either be for an insert or an update separately, not at the same time. I just meant I wanted to know how to do both.

Thanks!

[jgrannis]

Re: xBasic Insert Multiple Rows One Statement

Ok. Even if you can separate the inserts and updates, you still might want to build your "UPDATE" SQL to look like:

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

As you pass in the primary keys to be updated, MySQL will know it is a duplicate and update rather than insert.

[ssmoore1]

Re: xBasic Insert Multiple Rows One Statement

Hi Jeff,

Thanks, but would you know how it looks in Xbasic? For example, what would an xBasic statement look like to insert two records at the same time?

[madtowng]

Re: xBasic Insert Multiple Rows One Statement

Where are you getting the data to insert ?
I often send data to a local txt file, then use LOAD DATA LOCAL INFILE
to insert them in one call.

[ssmoore1]

Re: xBasic Insert Multiple Rows One Statement

Thanks, I don't think that would work its an external MySQL database.

[Stan Mathews]

Re: xBasic Insert Multiple Rows One Statement

Hi Jeff,

Thanks, but would you know how it looks in Xbasic? For example, what would an xBasic statement look like to insert two records at the same time?

Are you looking for

https://www.alphasoftware.com/docume...ment/index.xml

[madtowng]

Re: xBasic Insert Multiple Rows One Statement

Scott,
The load data local refers to where the input file is (your computer vs the MySQL server).
The code below is based on fields terminated by <TAB> and lines terminated by <CRLF>.
Please supply the real filename and table information. If you decide to not include the column
headings, change the ignore 1 lines to ignore 0 lines.

load data local infile 'c:\\directory\\filename.txt'
into TABLE destTable
fields terminated by '\t'
lines terminated by '\n'
ignore 1 lines
(Field1, Field2, Field3)
;

[madtowng]

Re: xBasic Insert Multiple Rows One Statement

Scott,
The load data local refers to where the input file is (your computer vs the MySQL server).
The code below is based on fields terminated by <TAB> and lines terminated by <CRLF>.
Please supply the real filename and table information. If you decide to not include the column
headings, change the ignore 1 lines to ignore 0 lines.

load data local infile 'c:\\directory\\filename.txt'
into TABLE destTable
fields terminated by '\t'
lines terminated by '\n'
ignore 1 lines
(Field1, Field2, Field3)
;

[jgrannis]

Re: xBasic Insert Multiple Rows One Statement

To hopefully clear up any confusion....
Since you asked "how do you insert/update multiple rows with one SQL statement" I ignored the "in xBasic" part because the "with one SQL statement" seemed to make it a pure SQL syntax question.

In this case, the xBasic that would execute the single "update multiple records" query doesn't need to look any different than if you were executing a query that updates just one record. It is the building of the SQL query string that simply requires more work.

On a high-level, you would build your SQL statement string to look like:
INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

Start with the string: "INSERT INTO table (id,Col1,Col2) VALUES "
Then loop through to build a string with all the values to match the format above and then append it to the original string.
Then append to the string: " ON DUPLICATE KEY UPDATE "
And then loop through to build the last part of the SQL string and finish with having the single SQL statement for multiple updates to use the same way you would use a single SQL statement to update a single record.

[ssmoore1]

Re: xBasic Insert Multiple Rows One Statement

Cool! Let me give that a shot!