How can I move a row from one table into another table?

[FrankD]

I have a database with three tables. All three tables are not identical, however they all have the same fields that I'm trying to transfer. For example (short example), let <br>
<br>
Table 1: <br>
FName<br>
LName<br>
MInit<br>
<br>
Table 2:<br>
FName<br>
LName<br>
MInit<br>
Address<br>
City<br>
State<br>
Zip<br>
<br>
What I need to be able to do is move everything that's in table one over to the same fields in table two, not minding the extras. I would use a normal insert sql statement, however in reality there's about 100 fields, and there has to be something easier than typing in each field, isn't there? <br>
<br>
Thanks.<br>

 

[peekay]

I presume you mean typing in the data of each field. Also not sure whether the record numbers of the same entries are the same.<br>
Have you tried a short routine which establishes a recordset in one table, read one field at a time from that table and writing it into another recordset of the other table

 

[FrankD]

Actually, I don't need to type anything in. The database already has stuff in it, I just need to (once I'm done using it) to move it over to a different table. Table 1 is &quot;Pending&quot; and table 2 is &quot;Processed&quot;. Processed just happens to have a few more fields. It's kind of confusing.<br>
As far as any routines, I don't really know where to start, other than a long, long, LONG SQL statement

 

[peekay]

Try this<br>
Open the pending table as a recordset<br>
open the processed table as a recorset<br>
start with a 'do while not eof' loop with records in pendingrecordset<br>
for every record read in this recordset search for the relevant record in the processed recordset (by way of sql maybe)<br>
write the data of the record read from pending recordset into this record<br>
then move to next record in pending recordset and loop<br>
<br>

 

[bitbrain]

Try,<br>
<br>
INSERT INTO Table2<br>
SELECT Table1.*<br>
FROM Table1<br>
<br>

 

[calahans]

Hi FrankD,<br>
<br>
If I'm reading your original post correctly, you two tables are not matching. Therefore while bitbrain is correct(almost), you will probably get an error. Try:<br>
<br>
INSERT INTO TABLE2 (COL1,COL2,COL3) SELECT * FROM TABLE1<br>
<br>
You just need to specify the columns after the TABLE2 part, also make sure that when you select * you get the columns in the right order i.e. matching the way you have ordered them in the first part of your select statement.<br>
<br>
What I would suggest is that you should run your statement in ISQL until you have it correct - then implement it in your app.<br>
<br>
C

 

[FrankD]

Calahans,<br>
Thanks for the above info. You were dead on assuming the tables were different. As for the above code, I have tried it, but the new table comes up with nothing in it. Here is what the generated SQL that VB executes successfully looks like:<br>
<br>
SQL = &quot;INSERT INTO [Past Orders] (Order Number, BFname, BLName) Select * From [New Orders] where [order number] = 1&quot;<br>
<br>
Of course, there are more than the three fields (they are fine in the actual code, just didn't need to type it all).<br>
<br>
BTW, what is ISQL ? <br>
<br>
Thank you..

 

[calahans]

Hi FrankD,<br>
<br>
ISQL is SQL Server's interactive SQL scratchpad. You can direct enter SQL command's into this, and run them (the equivalent in Oracle is SQLPLUS). What you need to do is run your program up to the point that the SQL string is complete (which you seem to have done) and then print this out in the debug window. Next copy and paste the SQL and enter it in through your database enquiry tool. The problem could be that the table you are inserting into, which has more columns the the table you are inserting from, could have nulls disallowed in the columns which you are not inserting a value into (if you don't specify a value, nulls will be entered) - this would give an error. By directly entering the SQL via the tools you will get a more detailed problem description.<br>
<br>
Let me know if it still don't work<br>
<br>
C