Newbie: Help with INSERT error message

[steve]

Hi ,
can someone help me with this statement:

INSERT INTO Measurements (datetime, val, METEOid, ParamMeteo, Valide)
(
SELECT DATEADD(HH,0,Date), val1, MENVid, CodePol, Valide
FROM tblMeasQAHor
UNION ALL
SELECT DATEADD(HH,1,date), val2, MENVid, CodePol, Valide
FROM tblMeasQAHor
UNION ALL
SELECT DATEADD(HH,2,date), val3, MENVid, CodePol, Valide
FROM tblMeasQAHor
)

I`ve tried various variations with various error mesages. For the above
(latest version) I get:
Error in the INSERT INTO statement.

Also, is the dateadd function properly set? I am trying to *transpose* a
|Date| Value1 | ... |value24| table into a |Date_Time| Value| table, so i
need to add a corresponding time to the old date-only field depending on
which column i am reading.

TIA for any help!
-steve

 

[pietlinden]

the DateAdd parts are a bit off.
DateAdd("hh",1,Date)...

look up dateadd in the help or use the wizard (the wand) and you can
build all this in the QBE grid.

 

[steve]

Well this is the least of my problems.
Even if i remove DATEADD completely, i have basic problems with nesting.
No matter what i try (parentheses, semicolon, different arrangement) there
are always error messages.
Unless I only use one date:

INSERT INTO Measurements ( date_time, val, METEOid, ParamMeteo, Valide)

SELECT DateAdd("h",0,Date) AS Expr1, tblMeasQAHor.val1, tblMeasQAHor.MENVid,
tblMeasQAHor.CodePol, tblMeasQAHor.Valide
FROM tblMeasQAHor;


This works fine.
But I *Need* to select all fields: value1, value2, value3 , ..... value24.
So I need to INSERT from a UNION.

Its probably a stupid little synatx error but i cant fix it for the last 5
hours !

 

[pietlinden]

Oh, wait a minute... Could you back up a step or two and explain what
you're trying to do? Are you trying to do something like a little data
washing/normalizing? (so you're taking a subset of columns and writing
them into a normalized table or something)...

If that's the case, then you might want to go about it differently -
looping through the fields collection of a table and generating SQL
statements on the fly to append all the data you want to the table in
question.

So, could you explain in general terms *what* you are trying to
accomplish? Once I understand that, maybe I can help you with the
*how*.

 

[pietlinden]

what if you do this from code and loop through the 24 fields you need
to append? You could do this by walking the fields collection of the
tblMeasQAHor table...

This is UNTESTED, butchered code... as a matter of fact, it won't run
as is, but should give you a starting point....

INSERT INTO Measurements (datetime, val, METEOid, ParamMeteo, Valide)
0,Date), val1, MENVid, CodePol, Valide
FROM tblMeasQAHor
UNION ALL
SELECT DATEADD(HH,1,date), val2, MENVid, CodePol, Valide
FROM tblMeasQAHor
UNION ALL
SELECT DATEADD(HH,2,date), val3, MENVid, CodePol, Valide
FROM tblMeasQAHor
)

Okay, this is _logically_ the same as:

const strSTART_SQL as string = "INSERT INTO Measurements (datetime,
val, METEOid, ParamMeteo, Valide) VALUES (SELECT DATEADD("

dim strSQL as string
dim intCounter as integer

for intCounter = 1 to intSomeNumberOfvalFields
strSQL = strSTART_SQL & "[" &
db.TableDefs("tblMeasQAHor").Fields(intCounter).Name & "]" &....

'basically, you'd finish the rest of the SQL insert statement, and
then execute it.


DBEngine(0)(0).Execute strSQL, dbFailOnError
next intCounter

Basically, you're looping through the fields collection of your table,
so once you set the start and stop field numbers (careful, they're
zero-based, so count off as 0,1,2... not 1,2,3..), and then you just
use the code to loop through those, modifying the SQL statement onthe
fly and then executing them in sequence. No need for some big ugly
UNION statement. If your intention is to only insert unique records,
then you could create an outer join by using the Find Unmatched query
wizard and modifying it (in code) to suit your purposes.

Infinitely easier than a monster union query anyway. You'd just need
to test it on a small set of data (and into a new table, so you don't
mess anything up that you need to be right!), and then once you're sure
it's working, append to the real table.

HTH,
Pieter