Hey John! Would this work? Sounds like Novak created the query in CF (ie with QueryNew()), so I don't know if SQL server could "see" the query object for an insert...select statement.
We've done this before by looping through but I don't know that QoQ could do this. Good to "see" ya again, talk to ya soon! Matt > -----Original Message----- > From: John Ceci [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, November 20, 2002 1:23 PM > To: CF-Talk > Subject: RE: BRAIN TEASER: Inserting a query into a table? > > > Novak, > > Here ya go, works on MS SQL Server 7.0 > > <cfquery name="BulkInsert" datasource="#ODBC_DataSource#"> > INSERT INTO TableName(userid, username, password) > SELECT DISTINCT userid, username, password FROM Users_Main > </cfquery> > > NOTE: The column list in the select statement must pass all > the rules for > insertion into the table, IE, data type, data length, ability > to be NULL > etc... > > HTH, > John > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, November 19, 2002 9:31 PM > To: CF-Talk > Subject: BRAIN TEASER: Inserting a query into a table? > > > Hi All, > > Here's an interesting twist. I have a "virtual" query (for > lack of a better > term) that I created and populated in ColdFusion using the > various query > functions in CFMX. I can "CFDUMP" the query on the screen > and everything > looks perfect. > > BUT... what I really want to do is dump the contents of that > query into an > SQL Server 2000 database. I'd prefer something easy and > automated such as: > > INSERT INTO table > SELECT * FROM newQuery > > I'd prefer it if I didn't have to loop over each row in my > newQuery and > insert it one row at a time. Can this be done? Has anyone ever done > anything like this before? Some example code would be fantastic. > > -Novak > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
