I don't have any experience with MySQL, but I've heard that there's a
way to enable multiple statements in a single <cfquery>. Once thats
enabled, look at the MySQL docs for the syntax for putting multiple SQL
statements together. In SQL Server (which I use frequently), you would
do something like this:
<cfquery>
delete from mytable
go
insert into mytable
......
go
</cfquery>
or
<cfquery>
delete from mytable;
insert into mytable......; **Note the trailing semicolon
</cfquery>
You may be able to do something similar in MySQL. By putting both
statements into a single <cfquery>, you are allowing the DBMS to queue
up processing. Each statement shouldn't execute until the previous one
completes.
As to the blank row left behind, where are you seeing this? In a
<cfquery> dump or are you looking at the table through a MySQL
management tool?
Carl
On 3/2/2011 11:20 AM, Mallory Woods wrote:
> Carl its MySQL
>
> On Wed, Mar 2, 2011 at 2:00 PM, Carl Von
> Stetten<[email protected]>wrote:
>
>> What DBMS are you using? SQL Server, MySQL, Oracle, or MS Access?
>> Carl
>>
>> On 3/2/2011 10:43 AM, Mallory Woods wrote:
>>> Carl,
>>>
>>> I will try that. I had the truncate in a cftrans.. let me try them both
>> and
>>> see what I get.
>>>
>>> Nope.. still the same result. I have tried having both of them in one
>>> cftrans and also having them in their own cftrans block.
>>>
>>> The odd thing is.. When the truncate works, It removes all of the data
>> but
>>> leaves one blank record. Like blanks on all lines.
>>>
>>> Very odd.
>>>
>>> Thanks for the suggestion but that didn't do it. :(
>>>
>>>
>>>
>>> On Wed, Mar 2, 2011 at 1:30 PM, Carl Von Stetten<[email protected]
>>> wrote:
>>>
>>>> I wouldn't expect that the delete or truncate would still be running,
>>>> but if they are, wrapping all of the<cfquery> tags in a<cftransaction>
>>>> might help, or wrapping the delete/truncate in a transaction, then
>>>> calling the insert in a separate<cftransaction>?
>>>>
>>>> HTH,
>>>> Carl
>>>>
>>>> On 3/2/2011 9:57 AM, Mallory Woods wrote:
>>>>> I am trying to clear all of the data from a table before importing new
>>>> data.
>>>>> Example code:
>>>>>
>>>>> *
>>>>> <cfquery name="qryClear" datasource="datasource">
>>>>> TRUNCATE TABLE tablename
>>>>> </cfquery>
>>>>>
>>>>> --->
>>>>> <!---
>>>>> <cfquery name="qryDelete" datasource="datasource">
>>>>> delete from tablename
>>>>> </cfquery>
>>>>> --->
>>>>>
>>>>> <cfquery name="qryInsert" datasource="datasource">
>>>>> insert into tablename
>>>>> ......
>>>>> </cfquery>*
>>>>>
>>>>> I have tried to truncate and delete the data. This appears to work
>> fine.
>>>>> However, the next set of lines which is supposed to import the data
>> runs
>>>> but
>>>>> no data is imported.
>>>>> Could the delete or truncate function still be running when the import
>> is
>>>>> taking place? If so, what is the best suggested method to get both of
>>>> these
>>>>> functions to run after
>>>>> each other?
>>>>>
>>>>> Thanks in advance.
>>>>>
>>>>> Mallory Woods
>>>>>
>>>>>
>>>>>
>>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342697
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
