I want to move several thousand records from an access database to SQL server. I have created an identical table in SQL server. I cannot simply query the access table, loop over the query and insert the data into the new table. With thousands of records this would simply take too long and would cause some server problems. What I want to do is this ... First I query the access table. Then I do a single insert and in the sql statement reference the access table values with a subquery. Here it is so far.

<cfquery name="original" datasource="#session.datasource#">
SELECT FWCUTT, FWDAYC, FWPW, FWRBOA, FWROUT FROM TB753B
</cfquery>

<cfquery name="tableinsert" datasource="#session.datasource_websupport#">
INSERT INTO TB753B (FWCUTT, FWDAYC, FWPW, FWRBOA, FWROUT)
VALUES (SELECT FWCUTT, FWDAYC, FWPW, FWRBOA, FWROUT FROM original)
</cfquery>

Here is the problem. The subquery is referencing a query object, not a typical datasource. Querying a query is cool in MX. I do it all the time. but is there a way to reference this so I can subquery a query within that sql?

Thanks ahead of time.

I don't know of this will work with a query of a query, but I don't think it will. This is how you would do it in a single sql statement. The number of select cols has to match the insert cols, of course.

INSERT INTO TB753B (FWCUTT, FWDAYC, FWPW, FWRBOA, FWROUT)
SELECT FWCUTT, FWDAYC, FWPW, FWRBOA, FWROUT FROM original

If you have a huge amount of data to move, it may be more efficient to use DTS (data transformation services) in MS SQL Server to import the data. There is a wizard in enterprise manager. Browse to the database you want to import to and right click and look for the option to import data ...

You can do all kinds of cool things with that. Like create the DTS package to do the import and save it and schedule it as a job to run a X time. Lots more too like being able to normalize your data, etc.