Hey Matt! Didn't realize he was using a query sim, missed that....
Hmmm...not sure past a loop how else you would do it either...brute force is sometimes best :) John -----Original Message----- From: Matthew Fusfield [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 20, 2002 2:42 PM To: CF-Talk Subject: RE: BRAIN TEASER: Inserting a query into a table? 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
