Work Smarter, Not Harder: Adding multiple rows to a table in Jet SQL

Hi all

I had a table for metadata that I had 25 rows to add to. Simple solution, right? Nope.

INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '5', 'red, Likely, Major');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '6', 'red, Almost Certain, Moderate');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '7', 'red, Unlikely, Catastrophic');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '8', 'yellow, Occasional, Major');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '9', 'yellow, Likely, Moderate');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '10', 'yellow, Almost Certain, Minor');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '11', 'yellow, Rare, Catastrophic');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '12', 'yellow, Unlikely, Major');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '13', 'yellow, Occasional, Moderate');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '14', 'yellow, Likely, Minor');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '15', 'yellow, Almost Certain, Negligible');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '16', 'yellow, Rare, Major');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '17', 'yellow, Unlikely, Moderate');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '18', 'yellow, Occasiona,l, Minor');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '19', 'yellow, Likely, Negligible');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '20', 'yellow, Rare, Moderate');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '21', 'green, Unlikely, Minor');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '22', 'green, Occasional, Negligible');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '23', 'green, Rare, Minor');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '24', 'cyan, Moderate, Negligible');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '25', 'cyan, Rare, Negligible');

That only greets me with “Extra characters after SQL”. Argh. Trying multiple value lines fails too:

INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '5', 'red, Likely, Major'),
('risk', '6', 'red, Almost Certain, Moderate');
('risk', '7', 'red, Unlikely, Catastrophic');
('risk', '8', 'yellow, Occasional, Major');
('risk', '9', 'yellow, Likely, Moderate');
('risk', '10', 'yellow, Almost Certain, Minor');
('risk', '11', 'yellow, Rare, Catastrophic');
('risk', '12', 'yellow, Unlikely, Major');
('risk', '13', 'yellow, Occasional, Moderate');
('risk', '14', 'yellow, Likely, Minor');
('risk', '15', 'yellow, Almost Certain, Negligible');
('risk', '16', 'yellow, Rare, Major');
('risk', '17', 'yellow, Unlikely, Moderate');
('risk', '18', 'yellow, Occasiona,l, Minor');
('risk', '19', 'yellow, Likely, Negligible');
('risk', '20', 'yellow, Rare, Moderate');
('risk', '21', 'green, Unlikely, Minor');
('risk', '22', 'green, Occasional, Negligible');
('risk', '23', 'green, Rare, Minor');
('risk', '24', 'cyan, Moderate, Negligible');
('risk', '25', 'cyan, Rare, Negligible');

“Expected ;” it says. Then after a bit of googling, I get the idea to use subqueries with unions instead! Yeah… uh, no.

INSERT INTO metadata (metadata_group, metadata_text, metadata_desc)
SELECT 'risk', '5', 'red, Likely, Major'
UNION SELECT 'risk', '6', 'red, Almost Certain, Moderate'
UNION SELECT 'risk', '7', 'red, Unlikely, Catastrophic'
UNION SELECT 'risk', '8', 'yellow, Occasional, Major'
UNION SELECT 'risk', '9', 'yellow, Likely, Moderate'
UNION SELECT 'risk', '10', 'yellow, Almost Certain, Minor'
UNION SELECT 'risk', '11', 'yellow, Rare, Catastrophic'
UNION SELECT 'risk', '12', 'yellow, Unlikely, Major'
UNION SELECT 'risk', '13', 'yellow, Occasional, Moderate'
UNION SELECT 'risk', '14', 'yellow, Likely, Minor'
UNION SELECT 'risk', '15', 'yellow, Almost Certain, Negligible'
UNION SELECT 'risk', '16', 'yellow, Rare, Major'
UNION SELECT 'risk', '17', 'yellow, Unlikely, Moderate'
UNION SELECT 'risk', '18', 'yellow, Occasiona,l, Minor'
UNION SELECT 'risk', '19', 'yellow, Likely, Negligible'
UNION SELECT 'risk', '20', 'yellow, Rare, Moderate'
UNION SELECT 'risk', '21', 'green, Unlikely, Minor'
UNION SELECT 'risk', '22', 'green, Occasional, Negligible'
UNION SELECT 'risk', '23', 'green, Rare, Minor'
UNION SELECT 'risk', '24', 'cyan, Moderate, Negligible'
UNION SELECT 'risk', '25', 'cyan, Rare, Negligible';

I get a nice and unhelpful “syntax error” pointing to the first subquery. Huh, guess you need a table to select from for unions. let’s give that a spin:

INSERT INTO metadata (metadata_group, metadata_text, metadata_desc)
SELECT 'risk', '5', 'red, Likely, Major' FROM dual
UNION SELECT 'risk', '6', 'red, Almost Certain, Moderate' FROM dual
UNION SELECT 'risk', '7', 'red, Unlikely, Catastrophic' FROM dual
UNION SELECT 'risk', '8', 'yellow, Occasional, Major' FROM dual
UNION SELECT 'risk', '9', 'yellow, Likely, Moderate' FROM dual
UNION SELECT 'risk', '10', 'yellow, Almost Certain, Minor' FROM dual
UNION SELECT 'risk', '11', 'yellow, Rare, Catastrophic' FROM dual
UNION SELECT 'risk', '12', 'yellow, Unlikely, Major' FROM dual
UNION SELECT 'risk', '13', 'yellow, Occasional, Moderate' FROM dual
UNION SELECT 'risk', '14', 'yellow, Likely, Minor' FROM dual
UNION SELECT 'risk', '15', 'yellow, Almost Certain, Negligible' FROM dual
UNION SELECT 'risk', '16', 'yellow, Rare, Major' FROM dual
UNION SELECT 'risk', '17', 'yellow, Unlikely, Moderate' FROM dual
UNION SELECT 'risk', '18', 'yellow, Occasiona,l, Minor' FROM dual
UNION SELECT 'risk', '19', 'yellow, Likely, Negligible' FROM dual
UNION SELECT 'risk', '20', 'yellow, Rare, Moderate' FROM dual
UNION SELECT 'risk', '21', 'green, Unlikely, Minor' FROM dual
UNION SELECT 'risk', '22', 'green, Occasional, Negligible' FROM dual
UNION SELECT 'risk', '23', 'green, Rare, Minor' FROM dual
UNION SELECT 'risk', '24', 'cyan, Moderate, Negligible' FROM dual
UNION SELECT 'risk', '25', 'cyan, Rare, Negligible' FROM dual

Nope. At this point it would’ve been quicker to just enter the damn data. So let’s try our unioned subquery within ANOTHER subquery, giving the fields from the first query names so we can refer to them:

INSERT INTO metadata (metadata_group, metadata_text, metadata_desc)
SELECT metadata_group, metadata_text, metadata_desc FROM (
 SELECT 'risk' AS metadata_group, '5' as metadata_text, 'red, Likely, Major' as metadata_desc FROM dual
 UNION SELECT 'risk', '6', 'red, Almost Certain, Moderate' FROM dual
 UNION SELECT 'risk', '7', 'red, Unlikely, Catastrophic' FROM dual
 UNION SELECT 'risk', '8', 'yellow, Occasional, Major' FROM dual
 UNION SELECT 'risk', '9', 'yellow, Likely, Moderate' FROM dual
 UNION SELECT 'risk', '10', 'yellow, Almost Certain, Minor' FROM dual
 UNION SELECT 'risk', '11', 'yellow, Rare, Catastrophic' FROM dual
 UNION SELECT 'risk', '12', 'yellow, Unlikely, Major' FROM dual
 UNION SELECT 'risk', '13', 'yellow, Occasional, Moderate' FROM dual
 UNION SELECT 'risk', '14', 'yellow, Likely, Minor' FROM dual
 UNION SELECT 'risk', '15', 'yellow, Almost Certain, Negligible' FROM dual
 UNION SELECT 'risk', '16', 'yellow, Rare, Major' FROM dual
 UNION SELECT 'risk', '17', 'yellow, Unlikely, Moderate' FROM dual
 UNION SELECT 'risk', '18', 'yellow, Occasiona,l, Minor' FROM dual
 UNION SELECT 'risk', '19', 'yellow, Likely, Negligible' FROM dual
 UNION SELECT 'risk', '20', 'yellow, Rare, Moderate' FROM dual
 UNION SELECT 'risk', '21', 'green, Unlikely, Minor' FROM dual
 UNION SELECT 'risk', '22', 'green, Occasional, Negligible' FROM dual
 UNION SELECT 'risk', '23', 'green, Rare, Minor' FROM dual
 UNION SELECT 'risk', '24', 'cyan, Moderate, Negligible' FROM dual
 UNION SELECT 'risk', '25', 'cyan, Rare, Negligible' FROM dual
);

Success!

So there you have it. Oh, and to create those queries? Just dump the data into Notepad++ and do a replace with regular expressions.

That's Too Much Info!

Ramblings of an IT mad scientist

Work Smarter, Not Harder: Adding multiple rows to a table in Jet SQL

Leave a Reply Cancel reply