copy to larger table

[cwjrda]

Hi, thanks for letting me ask a question of experienced SQL users.My application is in Java and uses Jdbc:Odbc to connect to and use a Microsoft Access Database that was created outside my app. I use SQL to query and Java's ResultSet to get and update fields. There is a situation in which I need to start with a fresh (empty except for static tables) database and copy some tables from a database collected on earlier missions as a starting point for analysis. The problem arises that we are now using a newer version of the database than the one being copied from. One (or more) of the tables that I need to copy now has an added column that the old one did not. The following SQL works fine for copying when the tables are the same (newer source DB):INSERT INTO ContactNAV SELECT * FROM ContactNAV IN 'sourceDbfile'But when copying from the older DB we get the following error (exception):"[Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same."In my current implementation I query the entire table from the source DB into a ResultSet, extract all the fields, then create a new ResultSet and update the fields and insertRow into the new DB -- one row at a time. An undesirable side effect of this is that fields that are Null in the source DB get extracted as 0 (using getFloat or getInt) and written as such to the new DB. Also, this CAN'T be the most efficient way to do it.I have tried several variations on the above INSERT INTO statement but have not been successful in getting it to copy the table, leaving Nulls where they belong and inserting Null into the new field (column). Below is an example of when I tried to explicitly name the fields to be copied. Note that "Alt" is the added column.INSERT INTO ContactNAV (AutoID, ID, Time, Source, Status, RateOfTurn, SOG, COG, Lat, Lon, TrueHeading, Alt)SELECT AutoID, ID, Time, Source, Status, RateOfTurn, SOG, COG, Lat, Lon, TrueHeading, NULL AS AltFROM ContactNAV IN 'sourceDbfile'That's where I am asking for help. Is there a way to copy the entire table from the source to the new DB ignoring the difference in columns and inserting Null for the new columns.Thanks for your time and any pointers you can provide.Wayne

[[dx]]

Then try:

INSERT INTO ContactNAV SELECT col1, col2, etc FROM ContactNAV IN 'sourceDbfile'

[cwjrda]

Then try:

INSERT INTO ContactNAV SELECT AutoID, ID, Time, Source, Status, RateOfTurn, SOG, COG, Lat, Lon, TrueHeadingFROM ContactNAV IN 'sourceDbfile'

... and got the same "Number of query values and destination fields are not the same." exception.Then...

INSERT INTO ContactNAV SELECT AutoID, ID, Time, Source, Status, RateOfTurn, SOG, COG, Lat, Lon, TrueHeading, AltFROM ContactNAV IN 'sourceDbfile'

... and got "Too few parameters. Expected 1. "

[birbal]

i think you need to do something like..

INSERT INTO table1 (col1,col2,col3) SELECT col1,col2,col3 FROM table2

columns data type should match to do it sucessfully.if you are going to copy the whole table then it should match the column number as well as column data type

[cwjrda]

Maybe I was unclear with my long explanation. The database design has been updated and a new column added to the table. I am asking for a way to copy all rows from an existing table that DOES NOT have the same number of columns as the target table.Doesn't this happen all the time when a database is changed to accommodate more information? Doesn't somebody have to copy the data from the old database into the new one?

[birbal]

INSERT INTO table1 (col1,col2,col3) SELECT col1,col2,col3 FROM table2it does not mean that table1 has only col1,col2,col3..there may be col4,col5 and so on. and for table2 you can assume that there is only col1,col2,col3.you can also do something like this...

INSERT INTO table1 (col1,col2,col3) SELECT * FROM table2

both are quite same..but in the first case you can decide the sequence of the column names and in the second case you have to mention it as the database sequnce (table2 column sequnce)sequnce should be matched

[cwjrda]

Well, after more trial and error, I found a combination that works. Note the "Null AS Alt" in the SELECT phrase. Maybe it works because this way the SELECT gets the right number of fields to match the INTO table.

INSERT INTO ContactNAV  SELECT AutoID, ID, Time, Source, Status, RateOfTurn, SOG, COG, Lat, Lon, TrueHeading, Null AS Alt FROM ContactNAV IN 'sourceDbfile'

[davej]

Well, after more trial and error, I found a combination that works. Note the "Null AS Alt" in the SELECT phrase. Maybe it works because this way the SELECT gets the right number of fields to match the INTO table.

INSERT INTO ContactNAV  SELECT AutoID, ID, Time, Source, Status, RateOfTurn, SOG, COG, Lat, Lon, TrueHeading, Null AS Alt FROM ContactNAV IN 'sourceDbfile'

I am able to insert values into arbitrary columns in a new table in this manner but I have been unable to figure out how to broaden the table. In other words I would like to INSERT and then UPDATE the additional columns with another query, but I don't think it is possible. You do not insert Identity surrogate keys.INSERT INTO WEEKLYTIME (EmpIDI, ClockIn)SELECT EmpID, CardDate FROM TIMECARDWHERE PunchType = 10ORDER BY EmpID, ID;