Multiple INSERT INTO 3

[stefanhab]

Hello there,

I tried to code a simple Example but I already stopped in the beginner phase.

I created a Access-Table

CREATE TABLE Obst_alt (
ID COUNTER NOT NULL CONSTRAINT constraintName_pk PRIMARY KEY,
PROFIL_ID integer null,
FRAGE integer null,
MERKMAL integer null,
WERT char(20) null
);

and now, try to insert some data - what seems to be a quiet easy task, but it only woks out, when I use only one INSERT line. As soon as I have two, Access annotates, that there "comes one more line after the sql-statement"

Furthermore, it always tells me, that I insert a new line into the table, when I try to insert a new line. (what is quiet annoying)


INSERT INTO Obst_alt (PROFIL_ID, FRAGE, MERKMAL, WERT) VALUES (1,1,3,"Apfel")
INSERT INTO Obst_alt (PROFIL_ID, FRAGE, MERKMAL, WERT) VALUES (1,1,7,"Banane");
INSERT INTO Obst_alt (PROFIL_ID, FRAGE, MERKMAL, WERT) VALUES (1,2,1,"Ja");
INSERT INTO Obst_alt (PROFIL_ID, FRAGE, MERKMAL, WERT) VALUES (2,1,1,"Pflaume");
INSERT INTO Obst_alt (PROFIL_ID, FRAGE, MERKMAL, WERT) VALUES (2,1,4,"Birne");
INSERT INTO Obst_alt (PROFIL_ID, FRAGE, MERKMAL, WERT) VALUES (2,1,7,"Banane");
INSERT INTO Obst_alt (PROFIL_ID, FRAGE, MERKMAL, WERT) VALUES (2,2,2,"Nein");


Thanks a lot for any kind of help or annotation
Stefan

 

[genomon]

INSERT INTO Obst_alt (PROFIL_ID, FRAGE, MERKMAL, WERT) VALUES (1,1,3,"Apfel")
INSERT INTO Obst_alt (PROFIL_ID, FRAGE, MERKMAL, WERT) VALUES (1,1,7,"Banane");

You missed a semicolon after the first line.
To turn off "Furthermore, it always tells me, that I insert a new line into the table", go to Tools|Options|Edit/Find and uncheck the boxes under "Confirm".
Bon Apetit!

Always remember that you're unique. Just like everyone else.

 

[stefanhab]

hi thanks for your help.

especially with the unlocktip.

but the missing semicolon didnt help, since I already tried all versions (and I have much more lines)
...so being honest this posted line was a line where I tried it without semicolon (but it didnt help)

 

[gmmastros]

Try this.

INSERT INTO Obst_alt (PROFIL_ID, FRAGE, MERKMAL, WERT) 
Select 1,1,3,"Apfel"
Union All Select 1,1,7,"Banane"
Union All Select 1,2,1,"Ja"
Union All Select 2,1,1,"Pflaume"
Union All Select 2,1,4,"Birne"
Union All Select 2,1,7,"Banane"
Union All Select 2,2,2,"Nein"

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[stefanhab]

thanks

I tried it with and without semikolons, with parenthesises and qithoud but there always occured:

syntax error in INSERT INTO-statement

(attention: not literally, since I translated it from German - but that is what it meant)

 

[stefanhab]

oh, I googled for this UNION command (since I have never heard about it) and I found the following:


The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. (since WERT is char (20) whereas the other vars are integer)


Could it be a problem or is it unimportant?
(I never worked with the UNION command)

 

[stefanhab]

well it seems to me that UNION is more a kind of merging the databases (or do i mix something up)

 

[stefanhab]

hey, I found something that almost works:

INSERT INTO Obst_alt (PROFIL_ID, FRAGE, MERKMAL, WERT) VALUES (1,1,3,"Apfel" AND [1,1,7,"Banane"]);

but nor really, since Access asks me to enter a value for "Banane" ??? whysoever

 

[JerryKlmns]

Stefan

Access accepts one statement to be executed at a time, so the many INSERT in one query wont be digested. The ; is indead pointless but... we add it at the end.

About the UNION query, the ordinal position of all the fields selected for every united recordset (created from each SELECT statement), should have the same data type and not all the fields! Plus all recordsets should have the same number of fields.

Maybe you should use some VBA code now...

 

[stefanhab]

Hi, thanks for your reply.

Indeed I realized I have to work a bit more on my VBA skills (besides the SQL)...
...since I realized, that the following kind of expression wont work out in SQL???


FOR i=1 TO XXX (the value of the final ID, which I can look for manually)

SELECT * FROM fruits_new WHERE ID=i;
q=QUEST;
c=KAT;
INSERT INTO fruits_new (Q[q]C[c]) VALUES (1);


Kind regards
Stefan


PS: So what I was looking for finally was, that I can adress variables automatically (when they are called Q1C1 Q1C2 Q2C1 Q2C2 for example)

 

[JerryKlmns]

Stefan

Dim Q1 as Variant, Q2 as Variant,Q3 as Variant, Q4....
Dim i As Long

i=1
Q & i is not the variable Q1

But you could use an Array like

Dim Q(4) As Variant
Dim i As Long
Q(i)= Something here

Or in a loop
For lCounter = LBound(Q) To UBound(Q)
Q(lCounter)=57
Next lCounter

 

[stefanhab]

hi thanks for all your relies,

buti have found now a solution that suits best for the requirements.

and for those who are interested - or maybe people, who will search later through the discussion board,
here the solution:


Sub CONCAT()
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Working_Table")
If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
var1 = rs("KAPITEL_ID")
var2 = rs("FRAGE_ID")
var3 = rs("SUBFRAGE_ID")
var4 = rs("MERKMAL_ID")
var5 = var1 & "_" & var2 & "_" & var3 & "_" & var4
rs.Edit
rs.Fields("CONCAT") = var5
rs.Update
rs.MoveNext
Loop
End If
Set rs = Nothing
End Sub



many thanks and all the best
Stefan

 

[JerryKlmns]

Would you like it faster??

Sub CONCAT()
Dim strSQL As String
strSQL = "UPDATE Working_Table " & _
         "SET CONCAT= [KAPITEL_ID] & '_' & [FRAGE_ID] & '_' & [SUBFRAGE_ID] & '_' & [MERKMAL_ID] ;"

 CurrentDb.Execute strSQL

End Sub

Try it!

 

[stefanhab]

Hi JerryKlmns,

thanks for the code I tried it and it worked very well - but I can't tell you, if it was faster than the other one, since I tried it on my very old mainstation which is a bad competition.

But many thanks!

Stefan Habermehl