23. januar 2010 - 11:35Der er
15 kommentarer og 1 løsning
INSERT INTO og UNION
Jeg er i gang med at omlaegge en ikke-normaliseret database i Access 2007. Tabellen over de der frekventerer et velvaerdscenter har en raekke for hver familie som indeholder felter for telefonnummer, gsmnummer og partner-gsmnummer. Jeg laver en saerskilt tabel for telefonnumre og skal overfoere vaerdierne fra den gamle tabel. Naturligvis kan jeg lave tre saerskilte queries saasom (1) INSERT INTO Telefon SELECT gsm FROM Familie, (2) INSERT INTO Telefon SELECT [gsm partner] FROM Familie, o.s.v., men jeg forsoeger at lave en UNION query saaledes:
INSERT INTO Telefon SELECT gsm FROM Familie WHERE gsm IS NOT NULL UNION SELECT [gsm partner] FROM Familie WHERE [gsm partner] IS NOT NULL
Men der faar jeg syntaks fejl. Jeg har proevet alle kombinationer af paranteser. Union querien selv, altsaa uden INSERT INTO Telefon tillades uden problemer.
Er du sikker på, at en UNION tillader INSERT. UNION er vel en slags samling af poster. I din INSERT har du ikke noget kriterie for, hvor data skal indsættes. Bliver de indsat de rigtige steder.
Blot et par strøtanker uden de helt store databasetanker. Jeg får gæster og er væk det meste af eftermiddagen.
mugs, jeg lavede en rettelse omtrent samtidig med at du svarede.
Jeg er sikker paa at det jeg gjorde ikke er tilladt (i det mindste fik jeg syntaksfejl.) Det jeg soeger er en metode der er tilladt og hvor jeg i en enkel query kan indsaette to hold vaerdier, i dette tilfaelde gsm og [gsm partner].
Make a query which returns the result of your UNION query then make an append query which uses the first query to append the result to the telefon table.
INSERT INTO Telefon (nummer) SELECT * FROM ( SELECT gsm FROM Familie WHERE gsm IS NOT NULL UNION SELECT [gsm partner] FROM Familie WHERE [gsm partner] IS NOT NULL )
For your suggestion # 5 I also get syntax error. I get the error message (my translation from Dutch, the language for my Access application) "The instruction INSERT INTO contains the following unknown field name: gsm.
The 'funny' thing is that when I reopen the query then the application has in the sentence 'INSERT INTO Telefon (nummer)' deleted '(nummer)'. Also the application adds in this and in some other complex queries this at the end: 'AS [%$##@_Alias];' That looks like a curse in Donald Duck.
Then you say in #4: 'Make a query which returns the result of your UNION query then make an append query which uses the first query to append the result to the telefon table.' I made the UNION query (called Query1) and double-clicked it which produced a table containing all the values. How do I then 'append the result to the telefon table'? I tried 'INSERT INTO Telefon(nummer) Query1' and 'INSERT INTO Telefon(nummer) SELECT * FROM Query1'
I just sent it. The table Mensen is the original table that I am splitting up. The telephone table is called 'Telefoont', thus with two o's following Dutch spelling.
Its on the way back. I figured out the names of the tables.
There are two examples, one using an extra query and the other where its all in one.
The name you mentioned 'AS [%$##@_Alias is a temporary name that Access gives it unless you give it a name yourself as I have here.(AS TempTable). So when you open the query you actually see a temporary table with this name.
And here is the query
INSERT INTO Telefoon ( nummer ) SELECT TempTable.gsm FROM (SELECT gsm FROM mensen WHERE gsm IS NOT NULL UNION SELECT [gsm partner] FROM mensen WHERE [gsm partner] IS NOT NULL) AS TempTable;
I was trying to understand why it works. The union select creates a virtual table that is called TempTable with one field which apparantly takes its name from the first query. If the union select query had started with [gsm partner] I would have had to say: INSERT INTO Telefoon(nummer) SELECT TempTable.[gsm partner].
Please make a 'svar' for points.
And a warning: I shall likely have more questions as I get along.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.