INSERT INTO problem

[cairo207]

I am trying to move data from table tblLandD to table tblOpen. tblLandD consists of EvalNo, ScenNo, ScenArea, Score, Comments, Analysis and all the data in this table where Analysis is equal to ‘Open’ needs to be copied into tblLandD.

tblOpen consists of OpenID, EvalNo, ScenNo, ScenArea, Score, Comments, Analysis, Resp, Resp_Given.

I have tried using the INSERT INTO statement but without success. Can anyone help this inexperienced database designer?

 

[jrbarnett]

Firstly, create the query to read the correct data from tblLandD.
The SQL code will be something like:

SELECT EvalNo, ScenNo, ScenArea, Score, Comments, Analysis
FROM tblLandD
WHERE Analysis = 'Open'

Then, add the insert statement at the top, putting appropriate fields in place:

INSERT INTO tblOpen (EvalNo, ScenNo, ScenArea, Score, Comments, Analysis)
SELECT EvalNo, ScenNo, ScenArea, Score, Comments, Analysis
FROM tblLandD
WHERE Analysis = 'Open'

The fields in tblOpen where no value is specified will use either the default value or Null as appropriate.

John

 

[JBinQLD]

GDay mate,

Try this (excuse typos - pseudo code)

INSERT INTO tblOpen ( EvalNo, ScenNo, ScenArea, Score, Comments, Analysis, Resp, Resp_Given)
SELECT EvalNo, ScenNo, ScenArea, Score, Comments, Analysis
FROM tblLandD
WHERE tblLandD.analysis="open";

Laterz

 

[cairo207]

Thanks for the help - worked a treat. Much appreciated.