Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Multi-Row INSERT

Posted on 2011-09-16
31
Medium Priority
?
374 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 400 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 400 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

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

Assisted Solution

by:HainKurt
HainKurt earned 800 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 59

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 59

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 59

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 59

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 59

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 59

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 59

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 59

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 59

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 59

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 59

Expert Comment

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

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 59

Assisted Solution

by:HainKurt
HainKurt earned 800 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 400 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

722 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