Hey Paul,
I'm looking at possibly doing this myself soon...
Couple of things I'm considering:
1) Draw a query in CF and use that query to generate a series of .cfm files
containing the necessary queries to insert data into the table.
The good news is that you shouldn't run into security / permissions issues.
The bad news is it's tedious, and requires manually setting the
identity_insert off on the table prior to the insert and then setting it
back on afterward -- at least in MS SQL Server anyway -- it's probably
different in other db's.
2) Use the bcp.exe bulk-copy feature and the master..xp_cmdshell stored
procedure to write the contents of the table to a file, then read that file
into the db using the reverse process. The good news is it's fast and
efficient. The bad news is that the cf-server needs permission to use the
master..xp_cmdshell stored procedure, which it probably won't have in any
shared environment, and may not have in some others.
At this point, I'm leaning toward option 1) ...
<cfquery name="rscontent">...</cfquery>
<cfset variables.left = chr(asc(","))>
<cfoutput query="rscontent">
<cfsavecontent variable="variables.cfm">
#left#cfquery>
SET IDENTITY_INSERT mytable ON;
INSERT INTO mytable ( #rscontent.columnlist# )
VALUES ( ... );
SET IDENTITY_INSERT mytable OFF;
#left#/cfquery>
</cfsavecontent>
<cffile action="write" output="#variables.cfm#"
file="#numberformat(currentrow,"0000")#_mytable.cfm">
</cfoutput>
Make sure that you escape single-quotes in any string values being inserted,
since they'll be literal values in the generated cfm, so they won't be
automatically escapes as they normally are with <cfquery> ... or use
<cfqueryparam> and make sure you escape any double-quotes to prevent them
breaking the <cfqueryparam> attribute and causing an invalid parser
construct error.
Once you've got these files, just use <cfdirectory> to get the list, then
include them each in order and that will give you your default data set.
hth
S. Isaac Dealey
Certified Advanced ColdFusion 5 Developer
www.turnkey.to
954-776-0046
> No, I can export the 'data' but not the actual SQL inserts and data
> combined
> .. at least I can't find how to do it .... I want to include some of this
> information into an install script that has a number of default values
> (too
> many to type in if it can be avoided).
> Thank You
> Paul Giesenhagen
> ----- Original Message -----
> From: "Bartee Lamar" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Friday, September 20, 2002 5:01 AM
> Subject: RE: OT Generating a data/Enterprise Manager
>> Would DTS Work for you.
>>
>> Bartee Lamar
>> www.enterpriseenergy.com
>> MSN [EMAIL PROTECTED]
>>
>>
>>
>> -----Original Message-----
>> From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]]
>> Sent: Friday, September 20, 2002 1:16 AM
>> To: CF-Talk
>> Subject: OT Generating a data/Enterprise Manager
>>
>>
>> Hey Folks!
>>
>> I am wanting to export a table and all the data in it into a SQL script
>> that can be ran to import the data into another database.
>>
>> I can script the table creation, and dump the data to a file, but how
>> can I combine the two together so I can run a 'script' and have it
>> create the table and import the values at the same time? Is it
>> possible?
>>
>> Thanks
>> Paul Giesenhagen
>> QuillDesign
>>
>>
>>
>>
>>
>
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
