Solved

Multi-Row INSERT

Posted on 2011-09-16
31
358 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
  • 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 92

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
 

Author Comment

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

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 92

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 51

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 51

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 51

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 51

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 51

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 51

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 51

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 51

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 51

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 51

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 51

Expert Comment

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

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 51

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 84

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 84
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now