Any code for automatically inserting queries of fi

gilgalbiblewheel · September 2nd, 2004, 09:53 PM

Is there a way of inserting the query of records from the first database into the second? Because I want to use the second db by deleting records that might be unnecessary.

sinapra · September 3rd, 2004, 12:09 AM

is it MSSQL. You can use stored proc and connect to both the dbs. then you can use the aliases of the DB's to select and insert statements.

I just gave you the outlines, hope this gives you some idea. Stored Proc are the best secured way to write such transactions.

Regards

sinapra

happygv · September 3rd, 2004, 02:46 PM

Do you mean from one database to the other or from one table to the other within same database(be it access or mssql)? Can you explain more on that?

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · September 3rd, 2004, 06:31 PM

Sorry. I meant from one table to the other. And I use MS Access 2000.

happygv · September 3rd, 2004, 06:38 PM

Try this.

Code:

Insert into DestinationTableName
Select Column1, Column2,....
from SourceTableName
Where Columnx=ValueX

Where Column1 of Source should match with Column1 or Destination in all aspects(Datatype, Order in which it is created in the table, etc...) Else, it would fail resulting errors.

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · September 3rd, 2004, 07:19 PM

Ok what about the ID column, which is the first column? Do you think it would be a good idea to renumber them, or to use the old numbers?

gilgalbiblewheel · September 3rd, 2004, 07:24 PM

Is Where Columnx=ValueX to be left as is or do I have to replace them with something?

gilgalbiblewheel · September 3rd, 2004, 07:27 PM

I got
Microsoft VBScript compilation error '800a0401'

Expected end of statement

/amos3.asp, line 134

Insert into results
------------^

gilgalbiblewheel · September 3rd, 2004, 07:35 PM

(4th reply after your reply)

Can I put * instead of naming all the fields?

happygv · September 4th, 2004, 12:12 PM

For your first reply : I would prefer to go with old numbers for the ID column, as that would be easy in case you want to refer/compare to the old table in any case.

For your second reply : That was a sample I posed. In case you want to insert rows based on some criteria, that is how you can use. So you got to replace COLUMNX with your original ColumnName and ValueX with the value, you would like to compare with.

For your 3rd reply : You got to post the entire query here, to look into it and suggest you where you go wrong.

For your 4th reply : You can put * after the SELECT keyword if you would like to port all the columns into the resulting table, else you will have to list them one by one, in the order as desgined in the resulting table.

In case the Resulting table is not created already, you can use

Code:

SELECT COLUMNLIST INTO NEWTABLENAME
FROM OLDTABLENAME WHERE COLUMNAMEX=VALUE

All in BLUE above are subject to change based on your requirement.

SELECT INTO is similar to INSERT INTO except that it creates a newtable as mentioned and then inserts the rows, but INSERT INTO inserts rows into existing table.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection