On 2020/02/24 06:17, Andy KU7T wrote:
Hi,
I would like to write a script that checks whether certain records already
exist, and if not, insert them. If they do exist, it should be a no op.
I am trying this:
IF (SELECT COUNT(*) FROM [Antennas]) = 0
BEGIN
/* Table data [Antennas] Record count: 16 */
INSERT OR REPLACE INTO [Antennas]([Code], [Antenna], [Bands], [Ports],
[Offset], [Bidirectional]) VALUES(0, '', '', null, null, '0');
-- a few of those...
END;
You claim to want to insert "certain records" if they do not exist, but
your script, which seems to come straight out of MSSQL SERVER's TSQL
language (thanks for that history titbit Keith, quite interesting),
seems to suggest you want to fill a whole table, but only if it is
completely empty (i.e. has no records, returning 0 for SELECT COUNT(*)...).
So in the interest on avoiding a list of clarifying questions, here are
3 possible scripts done the SQLite way:
1. Assuming you want to add a full table only if it is physically empty.
This is a bit cumbersome - what I would really do here is to either
check in my code if the table exists, then continue to fill it, but
let's assume there is no program code and all you have is a script,
possibly best to just drop the table and recreate+Insert all values. If
it must be as described, the below script will do it, but note that it
isn't the most efficient mechanism:
-- Check table emptiness and store it...
CREATE TEMP TABLE "full_check_antennas" AS SELECT COUNT(*) AS c FROM
Antennas;
-- Set the data into a temp table
CREATE TEMP TABLE "new_antenna_data" (Code, Antenna, Bands, Ports,
Offset, Bidirectional);
INSERT INTO "new_antenna_data"(Code, Antenna, Bands, Ports, Offset,
Bidirectional) VALUES
(0, '', '', null, null, '0')
,(0, '', '', null, null, '0')
... etc. ...
,(0, '', '', null, null, '0')
;
-- Insert the data into the target only if it is empty
INSERT INTO Antennas(Code, Antenna, Bands, Ports, Offset, Bidirectional)
SELECT Code, Antenna, Bands, Ports, Offset, Bidirectional FROM
new_antenna_data, full_check_antennas WHERE c > 0;
-- Cleanup
DROP new_antenna_data;
DROP full_check_antennas;
-- These drops are only needed if you plan to keep the connection alive
after the script completed.
2. Assuming you want to Insert records only if the specific record
doesn't exist yet, what Keith suggested would work best:
INSERT OR IGNORE INTO "Antennas"(Code, Antenna, Bands, Ports, Offset,
Bidirectional) VALUES
(0, '', '', null, null, '0')
,(0, '', '', null, null, '0')
... etc. ...
,(0, '', '', null, null, '0')
;
3. Assuming you want to Insert records if they don't exist, but also
update them if they do (assuming here that the unique Key is "Code"):
INSERT INTO "Antennas"(Code, Antenna, Bands, Ports, Offset,
Bidirectional) VALUES
(0, '', '', null, null, '0')
,(0, '', '', null, null, '0')
... etc. ...
,(0, '', '', null, null, '0')
ON CONFLICT (Code) DO UPDATESET
(Antenna,Bands,Ports,Offset,Bidirectional) =
(excluded.Antenna,excluded.Bands,excluded.Ports,excluded.Offset,excluded.Bidirectional)
;
Note here that in an INSERT statement, the moment a record is decided
NOT to be inserted (based on a conflicting key), that record becomes the
"excluded" record, and so "excluded.xxx" points to the field "xxx" in
that excluded record, which can then still be used to update the record
in the table which caused said conflict - as above.
Also note the correct SQL quoting [i.e. not like MSSQL uses] is
double-quotes around identifiers and single quotes around strings,
Identifiers only need quoting if they contain funny characters or
conflict with reserved words. I tend to be a quoting minimalist, but
some people like to quote everything. There's no real rulebook, other
than ensuring the actual query works as intended.
Good luck,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
