Multi-Row INSERT

Hello Experts,

I have the code below, that i get
?strsql
INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) VALUES (488, 0), (274, 0), (315, 0), (416, 0), (231, 0), (273, 0), (270, 0), (380, 0), (343, 0), (403, 0), (402, 0), (368, 0), (49, 0), (224, 0), (570, 0), (522, 0), (159, 0), (75, 0), (414, 0), (56, 0), (355, 0), (357, 0), (365, 0), (10, 0), (515, 0), (41, 0), (349, 0), (527, 0), (77, 0), (252, 0), (215, 0), (40, 0), (325, 0), (508, 0), (202, 0), (419, 0), (342, 0), (180, 0), (118, 0), (237, 0), (168, 0), (25, 0), (140, 0), (433, 0), (198, 0), (428, 0), (238, 0), (86, 0), (235, 0), (260, 0), (37, 0), (189, 0), (178, 0), (186, 0), (460, 0), (529, 0), (310, 0), (475, 0), (548, 0), (139, 0), (496, 0), (363, 0), (528, 0), (328, 0), (247, 0), (501, 0), (107, 0), (491, 0), (8, 0), (500, 0), (85, 0), (546, 0), (116, 0), (170, 0), (575, 0), (471, 0), (465, 0), (398, 0), (498, 0), (286, 0), (559, 0), (79, 0), (135, 0), (436, 0), (201, 0), (151, 0), (561, 0), (435, 0), (362, 0), (442, 0), (46, 0), (541, 0), (540, 0), (331, 0), (280, 0), (111, 0), (533, 0), (424, 0), (467, 0), (207, 0), (209, 0), (564, 0), (165, 0), (577, 0), (97, 0), (530, 0), (493, 0), (94, 0), (346, 0), (225, 0), (52, 0), (271, 0), (461, 0), (483, 0), (353, 0), (177, 0), (83, 0), (336, 0), (222, 0), (95, 0), (78, 0), (505, 0), (164, 0), (267, 0), (158, 0), (69, 0), (347, 0), (454, 0), (377, 0), (26, 0), (476, 0), (206, 0), (333, 0), (109, 0), (492, 0), (532, 0), (480, 0), (431, 0), (295, 0), (157, 0), (462, 0), (216, 0), (495, 0), (553, 0), (264, 0), (293, 0), (250, 0), (32, 0), (572, 0), (129, 0), (62, 0), (257, 0), (511, 0), (1, 0), (314, 0), (526, 0), (210, 0), (253, 0), (290, 0), (259, 0), (573, 0), (193, 0), (384, 0), (354, 0), (15, 0), (7, 0), (340, 0), (29, 0), (373, 0), (389, 0), (12, 0), (133, 0), (80, 0), (278, 0), (359, 0), (421, 0), (367, 0), (494, 0), (141, 0), (547, 0), (459, 0), (103, 0), (143, 0), (160, 0), (453, 0), (42, 0), (236, 0), (244, 0), (415, 0), (64, 0), (479, 0), (458, 0), (282, 0), (65, 0), (513, 0), (499, 0), (566, 0), (145, 0), (388, 0), (478, 0), (275, 0), (551, 0), (390, 0), (313, 0), (71, 0), (73, 0), (378, 0), (519, 0), (283, 0), (51, 0), (120, 0), (16, 0), (100, 0), (256, 0), (535, 0), (230, 0), (481, 0), (174, 0), (397, 0), (338, 0), (538, 0), (327, 0), (425, 0), (24, 0), (265, 0), (285, 0), (472, 0), (117, 0), (412, 0), (288, 0), (60, 0), (558, 0), (399, 0), (105, 0), (456, 0), (191, 0), (485, 0), (542, 0), (358, 0), (99, 0), (223, 0), (172, 0), (263, 0), (185, 0), (408, 0), (184, 0), (455, 0), (306, 0), (67, 0), (417, 0), (39, 0), (11, 0), (249, 0), (169, 0), (322, 0), (54, 0), (299, 0), (370, 0), (144, 0), (82, 0), (220, 0), (268, 0), (179, 0), (426, 0), (131, 0), (154, 0), (35, 0), (303, 0), (137, 0), (114, 0), (96, 0), (245, 0), (279, 0), (72, 0), (156, 0), (205, 0), (134, 0), (124, 0), (57, 0), (308, 0), (50, 0), (34, 0), (43, 0), (563, 0), (562, 0), (490, 0), (19, 0), (239, 0), (262, 0), (233, 0), (464, 0), (514, 0), (127, 0), (66, 0), (439, 0), (300, 0), (350, 0), (404, 0), (567, 0), (248, 0), (166, 0), (352, 0), (171, 0), (218, 0), (48, 0), (136, 0), (555, 0), (307, 0), (560, 0), (332, 0), (287, 0), (132, 0), (258, 0), (242, 0), (525, 0), (47, 0), (246, 0), (68, 0), (451, 0), (9, 0), (356, 0), (150, 0), (393, 0), (110, 0), (468, 0), (524, 0), (510, 0), (413, 0), (227, 0), (337, 0), (187, 0), (115, 0), (449, 0), (298, 0), (411, 0), (578, 0), (549, 0), (394, 0), (369, 0), (466, 0), (444, 0), (167, 0), (181, 0), (284, 0), (477, 0), (574, 0), (432, 0), (509, 0), (554, 0), (379, 0), (531, 0), (543, 0), (447, 0), (463, 0), (450, 0), (568, 0), (445, 0), (366, 0), (406, 0), (539, 0), (517, 0), (438, 0), (391, 0), (550, 0), (523, 0), (401, 0), (434, 0), (381, 0), (474, 0), (429, 0), (506, 0), (364, 0), (376, 0), (430, 0), (557, 0), (422, 0), (448, 0), (241, 0), (232, 0), (138, 0), (38, 0), (55, 0), (221, 0), (102, 0), (123, 0), (545, 0), (518, 0), (146, 0), (344, 0), (266, 0), (194, 0), (119, 0), (44, 0), (504, 0), (437, 0), (217, 0), (569, 0), (63, 0), (211, 0), (91, 0), (58, 0), (18, 0), (512, 0), (317, 0), (571, 0), (536, 0), (407, 0), (470, 0), (487, 0), (565, 0), (5, 0)
, (152, 0), (296, 0), (440, 0), (214, 0), (420, 0), (482, 0), (53, 0), (27, 0), (122, 0), (576, 0), (289, 0), (2, 0), (579, 0), (484, 0), (330, 0), (360, 0), (507, 0), (255, 0), (294, 0), (503, 0), (163, 0), (251, 0), (556, 0), (386, 0), (208, 0), (544, 0), (28, 0), (312, 0), (87, 0), (341, 0), (457, 0), (387, 0), (84, 0), (196, 0), (383, 0), (382, 0), (188, 0), (302, 0), (113, 0), (520, 0), (323, 0), (469, 0), (537, 0), (473, 0), (552, 0), (108, 0), (277, 0), (70, 0), (502, 0), (410, 0), (3, 0);


but i keep getting
Missing semicolon (;) at end of SQL statement.

Any ideas?
Private Sub cmdAddAll_Click()
   
    Dim strSQL As String
    Dim i As Integer
    Dim lngAgentID As Long
    
    Call cmdDelAll_Click
    
    strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) VALUES "
    
    For i = lstAllAgents.ListCount To 1 Step -1
    
        lngAgentID = lstAllAgents.Column(0, i - 1)
        
        strSQL = strSQL & "(" & lngAgentID & ", " & locID & "), "
        
    Next i
    
    strSQL = Left(strSQL, Len(strSQL) - 2) & ";"
    CurrentDb.Execute strSQL
    
    Call populateLocsAgents
    Call populateAllAgents
    
End Sub

Open in new window

APD TorontoAsked:
Who is Participating?
 
Michael VasilevskyConnect With a Mentor Solutions ArchitectCommented:
You need to insert each row one at a time, i.e.

For i = lstAllAgents.ListCount To 1 Step -1
   
        lngAgentID = lstAllAgents.Column(0, i - 1)
       
        strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) VALUES (" & lngAgentID & ", " & locID & "), "
CurrentDb.Execute strSQL

       
    Next i



0
 
Michael VasilevskySolutions ArchitectCommented:
Correction:
For i = lstAllAgents.ListCount To 1 Step -1
   
        lngAgentID = lstAllAgents.Column(0, i - 1)
       
        strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) VALUES (" & lngAgentID & ", " & locID & ")"
        CurrentDb.Execute strSQL

Next i

0
 
Patrick MatthewsConnect With a Mentor Commented:
Or set up your code to use a UNION query:

INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc)
SELECT 488 AS fldAGent, 0 AS fldLoc
UNION ALL
SELECT 274 AS fldAGent, 0 AS fldLoc
UNION ALL
SELECT 315 AS fldAGent, 0 AS fldLoc
UNION ALL
et cetera
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
APD TorontoAuthor Commented:
But i need a single .execute
0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
you dont need to give alias names to column when select

INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc)
SELECT 488,0
UNION ALL
SELECT 274, 0
UNION ALL
SELECT 315, 0
UNION ALL
...
0
 
Patrick MatthewsCommented:
>>But i need a single .execute

Why, exactly?  If I had to do this myself, I would probably do it as mvasilevsky did it above.
0
 
APD TorontoAuthor Commented:
it takes 45sec to execute
0
 
HainKurtSr. System AnalystCommented:
or to convert your str into the one I posted above, use these codes before running:

strsql = Replace(strsql,"VALUES (","select ")
strsql = Replace(strsql,"), ("," union all select ")
strsql = Replace(strsql,");","")

and make sure after all replaces, you end up

INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) VALUES select 488, 0 union all select 274, 0 union all ... union all select 3, 0
0
 
HainKurtSr. System AnalystCommented:
or, how do you get those tupples? are they in a table? if yes

INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) select a,b from myTable

Note: I guess you should remove values from my posts above :)

and make sure after all replaces, you end up

INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) VALUES select 488, 0 union all select 274, 0 union all ... union all select 3, 0

-->

and make sure after all replaces, you end up

INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) select 488, 0 union all select 274, 0 union all ... union all select 3, 0
0
 
APD TorontoAuthor Commented:
How would you rewrite my for...loop?
0
 
HainKurtSr. System AnalystCommented:
try this:

strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) "
    For i = lstAllAgents.ListCount To 1 Step -1
        lngAgentID = lstAllAgents.Column(0, i - 1)
        strSQL = strSQL & " select " & lngAgentID & ", " & locID
        if i <> 1 then strSQL = strSQL & " union all "
    Next i
0
 
HainKurtSr. System AnalystCommented:
and after loop, if it does not work post the value of query (just beginning & end part is enough)
0
 
APD TorontoAuthor Commented:
shouldnt if i <> 1 then strSQL = strSQL & " union all "

be if i <> 0 then strSQL = strSQL & " union all "

as 0 is the last item?
0
 
HainKurtSr. System AnalystCommented:
i = lstAllAgents.ListCount To 1

you end with 1 not 0... anyways, see what is the result...
0
 
APD TorontoAuthor Commented:
Sorry, long day, i will never be 0

now i get:
Syntax error (missing operator) in query expression '0 UNION ALL SELECT 274'.

my string is:
?STRSQL
INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) SELECT 488, 0 UNION ALL SELECT 274, 0 UNION ALL SELECT 315, 0 ... UNION ALL SELECT 3, 0
0
 
HainKurtSr. System AnalystCommented:
hmms, maybe we need to add (....) to make it

INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) (SELECT 488, 0 UNION ALL SELECT 274, 0 UNION ALL SELECT 315, 0 ... UNION ALL SELECT 3, 0)

0
 
HainKurtSr. System AnalystCommented:
try this:

strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) ("
For i = lstAllAgents.ListCount To 1 Step -1
  lngAgentID = lstAllAgents.Column(0, i - 1)
  strSQL = strSQL & " select " & lngAgentID & ", " & locID
  if i <> 1 then strSQL = strSQL & " union all " else strSQL = strSQL & ")"
  ' strSQL = strSQL & iif(i=1, ")", " union all ")
Next i
0
 
APD TorontoAuthor Commented:
INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) (SELECT 488, 0 UNION ALL SELECT 274, 0 ...UNION ALL SELECT 3, 0)

Syntax error in INSERT INTO statement.
0
 
APD TorontoAuthor Commented:
wgat about INSERT INTO...

fldAgent = SELECT fldAgentID WHERE fldArchived = False

fldLoc is always 0 in this case
0
 
HainKurtSr. System AnalystCommented:
ok :) this is getting strange but I tried with a local access

insert into roles (roleid, rolename)
select a,b from (
select 10 as a, 'A' as b from dual
union all select 20, 'B' from dual
union all select 30,'C' from dual
)

works fine... but I have a dual table with one column and one record dual(id) : 1 record = 1
so, add a table dual, with one column(int) and add one row into it

then try this and see if you get

strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) select a,b from ("
For i = lstAllAgents.ListCount To 1 Step -1
  lngAgentID = lstAllAgents.Column(0, i - 1)
  strSQL = strSQL & iif(i=1, " select " & lngAgentID & " as a, " & locID & " as b from dual ", " union all select " & lngAgentID & ", " & locID & " from dual ")
Next i

you should get:

INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) SELECT 488 as a, 0 as b from dual UNION ALL SELECT 274, 0 from dual ...UNION ALL SELECT 3, 0 from dual)

0
 
HainKurtSr. System AnalystCommented:
oops, forgot one ")" at the end

strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) select a,b from ("
For i = lstAllAgents.ListCount To 1 Step -1
  lngAgentID = lstAllAgents.Column(0, i - 1)
  strSQL = strSQL & iif(i=1, " select " & lngAgentID & " as a, " & locID & " as b from dual ", " union all select " & lngAgentID & ", " & locID & " from dual)")
Next i
0
 
APD TorontoAuthor Commented:
i added
table = dual

field = int (long number)

rec, int =1

i get
Syntax error in FROM clause.

0
 
HainKurtSr. System AnalystCommented:
what is your new query now

it should look like this

insert into roles (roleid, rolename)
select a,b from (
select 10 as a, 'A' as b from dual
union all select 20, 'B' from dual
union all select 30,'C' from dual
)
0
 
APD TorontoAuthor Commented:
are you able to send me a sample? also, do i anbsolutely need dual?
0
 
APD TorontoAuthor Commented:
INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) select a,b from ( union all select 488, 162 from dual) union all select 274, 162 from dual) ... union all select 410, 162 from dual) select 3 as a, 162 as b from dual

i'm missing (
0
 
HainKurtSr. System AnalystCommented:
here is a sample db
EE.mdb
0
 
HainKurtSr. System AnalystCommented:
ok try this code and post the value
strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) select a,b from ("
For i = lstAllAgents.ListCount To 1 Step -1
  lngAgentID = lstAllAgents.Column(0, i - 1)

  if i=lstAllAgents.ListCount 
  then strSQL = strSQL & " select " & lngAgentID & " as a, " & locID & " as b from dual "
  else strSQL = strSQL & " union all select " & lngAgentID & ", " & locID & " from dual "
Next i
strSQL = strSQL & ")"

Open in new window

0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
I am not sure if we really need dual table, but without it access does not understand :)
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Why not just INSERT each item separately:
For i = lstAllAgents.ListCount To 1 Step -1

    strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) VALUES "

    lngAgentID = lstAllAgents.Column(0, i - 1)
        
    strSQL = strSQL & "(" & lngAgentID & ", " & locID & ")"
    Currentdb.Execute strSQL        
Next i

Open in new window

Assuming fldAgent and fldLoc are both numeric datatypes, that should work.

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Or even simpler:

For i = lstAllAgents.ListCount To 1 Step -1
    CurrentDB.Execute "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) VALUES(" & lstAllAgents.Column(0, i - 1) & "," & LocID & ")"
Next i 

Open in new window

0
 
APD TorontoAuthor Commented:
Inserting one by one takes 45 secs+

Solution

Dim strSQL As String
    
    Call cmdDelAll_Click
    
    strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) "
    strSQL = strSQL & "SELECT sQRY.fldAgentID, " & locID & " as fldLoc FROM (" & lstAllAgents.RowSource & ") as sQRY;"
    
    CurrentDb.Execute strSQL
    
    Call populateLocsAgents
    Call populateAllAgents

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.