Solved

Multi-Row INSERT

Posted on 2011-09-16
31
371 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:APD_Toronto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 11
  • 2
  • +2
31 Comments
 
LVL 10

Accepted Solution

by:
Michael Vasilevsky earned 100 total points
ID: 36552402
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
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 36552406
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
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 100 total points
ID: 36552410
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:APD_Toronto
ID: 36552422
But i need a single .execute
0
 
LVL 56

Assisted Solution

by:HainKurt
HainKurt earned 200 total points
ID: 36552427
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36552431
>>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
 

Author Comment

by:APD_Toronto
ID: 36552435
it takes 45sec to execute
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 36552437
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
 
LVL 56

Expert Comment

by:HainKurt
ID: 36552447
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
 

Author Comment

by:APD_Toronto
ID: 36552465
How would you rewrite my for...loop?
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 36552470
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
 
LVL 56

Expert Comment

by:HainKurt
ID: 36552473
and after loop, if it does not work post the value of query (just beginning & end part is enough)
0
 

Author Comment

by:APD_Toronto
ID: 36552495
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
 
LVL 56

Expert Comment

by:HainKurt
ID: 36552507
i = lstAllAgents.ListCount To 1

you end with 1 not 0... anyways, see what is the result...
0
 

Author Comment

by:APD_Toronto
ID: 36552540
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
 
LVL 56

Expert Comment

by:HainKurt
ID: 36552548
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
 
LVL 56

Expert Comment

by:HainKurt
ID: 36552557
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
 

Author Comment

by:APD_Toronto
ID: 36552593
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
 

Author Comment

by:APD_Toronto
ID: 36552609
wgat about INSERT INTO...

fldAgent = SELECT fldAgentID WHERE fldArchived = False

fldLoc is always 0 in this case
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 36552653
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
 
LVL 56

Expert Comment

by:HainKurt
ID: 36552657
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
 

Author Comment

by:APD_Toronto
ID: 36552671
i added
table = dual

field = int (long number)

rec, int =1

i get
Syntax error in FROM clause.

0
 
LVL 56

Expert Comment

by:HainKurt
ID: 36552680
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
 

Author Comment

by:APD_Toronto
ID: 36552683
are you able to send me a sample? also, do i anbsolutely need dual?
0
 

Author Comment

by:APD_Toronto
ID: 36552686
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
 
LVL 56

Expert Comment

by:HainKurt
ID: 36552690
here is a sample db
EE.mdb
0
 
LVL 56

Expert Comment

by:HainKurt
ID: 36552695
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
 
LVL 56

Assisted Solution

by:HainKurt
HainKurt earned 200 total points
ID: 36552697
I am not sure if we really need dual table, but without it access does not understand :)
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 36553709
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
 
LVL 85
ID: 36553712
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
 

Author Comment

by:APD_Toronto
ID: 36554572
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question