[mailto:afpwebwo...@gmail.com]
Sent: Friday, October 10, 2014 1:51 AM
To: cf-talk
Subject: Re: Avoiding a boat load of queries inserting multiple records -
Better Way?
You do know that Microsoft changed the insert syntax as from SQLServer 2008
dont you?now you can insert multiple records in one go like
[mailto:afpwebwo...@gmail.com]
Sent: Friday, October 10, 2014 1:51 AM
To: cf-talk
Subject: Re: Avoiding a boat load of queries inserting multiple records -
Better Way?
You do know that Microsoft changed the insert syntax as from SQLServer 2008
dont you?now you can insert multiple records in one
-Original Message-
From: Mike K [mailto:afpwebwo...@gmail.com]
Sent: Friday, October 10, 2014 1:51 AM
To: cf-talk
Subject: Re: Avoiding a boat load of queries inserting multiple records -
Better Way?
You do know that Microsoft changed the insert syntax as from SQLServer 2008
dont you?now you
-talk
Subject: Re: Avoiding a boat load of queries inserting multiple records -
Better Way?
Not knowing much about your overall process, I'll try to stick to some
generic ideas. Let's assume you don't need this stuff dumped into the
database the very second it gets sent to you
On 10/10/2014 1:50 AM, Mike K wrote:
You do know that Microsoft changed the insert syntax as from SQLServer 2008
dont you?now you can insert multiple records in one go like this:
INSERT into Tablename (field1, field2, field3, field4 ) Values
( value1, value2, value3, value4 ... ;
Ideally I would also suggest SSIS for this, but sounds like a total
re-write might be out of the question.
This is what I would probably try, maybe breaking the number of records
done during one transaction into something reasonable depending on server
performance. Believe the OUTPUT clause is
I've got an application that imports email list from Excel sheets.
Mostly working fine, but I've got one spot where I'd like to optimize
things if I could.
Once the data is imported I run two queries against each email address:
1. see if the email address is already in the group in question
2.
On Thu, Oct 9, 2014 at 12:30 PM, Les Mizzell wrote:
Is there a better way to set up my two insert queries above so it's not
making two calls for every single address?
Most databases will let you issue multiple SQL statements in a single
request/transaction. You just have to separate them with
inserting multiple records - Better
Way?
I've got an application that imports email list from Excel sheets.
Mostly working fine, but I've got one spot where I'd like to optimize things
if I could.
Once the data is imported I run two queries against each email address:
1. see if the email address
Les,
I think that it would probably be more efficient if you imported all the
records into a temp table and then did an insert into the main email table
based upon a join query that only includes records from the temp table that
are not in the main email table. You could then do a similar insert
or just get all the email addresses from excel and send them over to a
tored procedure as a list to process in one go.
On Thu, Oct 9, 2014 at 7:22 PM, Dean Lawrence dean...@gmail.com wrote:
Les,
I think that it would probably be more efficient if you imported all the
records into a temp
Have you investigated using a Stored Procedure that does both inserts?
This seems the way to go.
Writing Transact-SQL is outside my area of expertise.
I'm looking at example code now.
Give me a bit and maybe I'll figure it out...
Thanks to everybody that replied.
On 10/9/2014 1:49 PM, Cameron Childress wrote:
Most databases will let you issue multiple SQL statements in a single
request/transaction. You just have to separate them with a semicolon.
If you did it this way, how would you get the ID from the first insert
for use in the 2nd ... (because I'm
On Thu, Oct 9, 2014 at 4:29 PM, Les Mizzell lesm...@bellsouth.net wrote:
If you did it this way, how would you get the ID from the first insert
for use in the 2nd ...
No I don't think that's going to work the way you want it to in that case.
A few years ago I stopped using numerics and
On 10/9/2014 5:00 PM, Cameron Childress wrote:
A few years ago I stopped using numerics and auto-increment and started
using UUID for all PK/FK columns.
On new stuff, this is what I'm doing as well. Unfortunitely, this is a
pre-existing app and would take a good bit of rewrite to do that.
What kind of database? MSSql? Mysql? Oracle? ...
--
William Seiter
-Original Message-
From: Les Mizzell [mailto:lesm...@bellsouth.net]
Sent: Thursday, October 09, 2014 1:29 PM
To: cf-talk
Subject: Re: Avoiding a boat load of queries inserting multiple
On 10/9/2014 5:16 PM, William Seiter wrote:
What kind of database? MSSql? Mysql? Oracle? ...
SQL Server 10
~|
Order the Adobe Coldfusion Anthology now!
-
From: Les Mizzell [mailto:lesm...@bellsouth.net]
Sent: Thursday, October 09, 2014 2:14 PM
To: cf-talk
Subject: Re: Avoiding a boat load of queries inserting multiple records -
Better Way?
On 10/9/2014 5:00 PM, Cameron Childress wrote:
A few years ago I stopped using numerics and auto-increment
On 10/9/2014 5:20 PM, William Seiter wrote:
Off the top of my head...
To insert the last inserted unique id in a transaction...
cfquery
INSERT dbo.table(column) SELECT 1;
SELECT @newID = SCOPE_IDENTITY();
INSERT dbo.table_2 (column) SELECT @newID;
/cfquery
I'll give this a try. It's
I would probably use a trigger to do the second insert.
On Thu, Oct 9, 2014 at 5:38 PM, Les Mizzell lesm...@bellsouth.net wrote:
I'll give this a try. It's got to be way less processor intensive than
running multiple queries for each insert.
Still, looks the stored procedure will be the less
Just to play devil's advocate for moment... This thread reminds me of the
old adage about what happens when you hire a carpenter for just any old
job, they tend to hit things with hammers. As web software developer, our
first instinct is to go out and write code.
Having been down this same
Not knowing much about your overall process, I'll try to stick to some
generic ideas. Let's assume you don't need this stuff dumped into the
database the very second it gets sent to you.
This is actually part of a client's admin system on their website. They
send out legal newsletters.
You do know that Microsoft changed the insert syntax as from SQLServer 2008
dont you?now you can insert multiple records in one go like this:
INSERT into Tablename (field1, field2, field3, field4 ) Values
( value1, value2, value3, value4 ... ;
value1, value2, value3, value4 ;
23 matches
Mail list logo
