Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL INSERT or CFINSERT 1
- Thread starter RSedlacek
- Start date
-
1
- #2
SidianConsulting
Programmer
Straight SQL code is always better, and in my opinion, there are no pros to using CFINSERT. Beginners mostly use this as a way to get up and running with simple SQL functionality in their apps, but in my opinion you are better off using straight SQL inside a cfquery, or better yet, stored procedures (not supported in Access, though).
People might argue that CFINSERT is simpler and easier to write, but it has been proven slower and on some of the older versions of CF (4.x), there were problems with it. In my mind it's a crutch that a beginner can do and should do without.
-Tek
People might argue that CFINSERT is simpler and easier to write, but it has been proven slower and on some of the older versions of CF (4.x), there were problems with it. In my mind it's a crutch that a beginner can do and should do without.
-Tek
Hey! I agree 100%, you get a star!
SQL INSERTs are easy.. you match the number of columns in the db with the number of fields and away you go..
Hey R, here's a little bit of help on the syntax of an insert query, if you need it..
SQL INSERTs are easy.. you match the number of columns in the db with the number of fields and away you go..
Hey R, here's a little bit of help on the syntax of an insert query, if you need it..
Code:
<!--- Name is not required for insert/update/delete queries and its just extra typing to specify a name as far as I'm concerned. --->
<CFQUERY datasource="MyDataBase
<!---The first set is column names.. Remember, do not use "'"s around these, a common error..--->
INSERT INTO Table_Name(Enter,Your,Column,Names,Here,In,Comma,Delimited,Format)
<!---The second set is variable names, from any variable scope available to the page, (IE: url, caller, application, request, session, form, etc)--->
VALUES('#Enter#','#Your#','#Variable#','#Names#','#Here#','#In#','#Comma#','#Delimited#','#Format#')
</CFQUERY>The person who created the SQL INSERT syntax should be shot. To amending an existing SQL INSERT you have to count along the field name list and then along the value list -a nightmare if the record is large. INSERT should have used the same name-value pair notation as UPDATE.
I use a custom tag from Macromedia's Tag Gallery which accepts name-value pairs and transforms them into a regular SQL INSERT - it's called CF_dbINSERT. I had to modify it because the original enclosed the code in a CFTRANSACTION block and I think defining transactions is a matter of application design, but otherwise it's saved me a lot of trouble.
I use a custom tag from Macromedia's Tag Gallery which accepts name-value pairs and transforms them into a regular SQL INSERT - it's called CF_dbINSERT. I had to modify it because the original enclosed the code in a CFTRANSACTION block and I think defining transactions is a matter of application design, but otherwise it's saved me a lot of trouble.
pilcher, excellent comment about the edit nightmare -- but that's only if you're removing a column, right? i mean, adding a column, i would just stick it at the front, you know? to be ornery? even if the new column's at the end of the table, you can list them in the INSERT syntax in whatever order you want, as long as you match the values in the same order!
hey webmigit, you don't actually have to match the number of table columns in an INSERT, you can leave some of them out (and in fact for autonumbers, this is recommended) -- you just have to provide a value for every NOT NULL field that has no DEFAULT declared
create table foo
( id integer primary key
, a varchar(20) not null default "i dunno"
, b integer not null
, c integer default 21
)
you can leave the column list off if you provide a value for every column in their exact order
insert into foo
values (1, 'it''s me', 641, 20)
but you can also do it by inserting only some of the values, letting the other ones default
insert into foo (b, id, c)
values (641, 2, null)
rudy
hey webmigit, you don't actually have to match the number of table columns in an INSERT, you can leave some of them out (and in fact for autonumbers, this is recommended) -- you just have to provide a value for every NOT NULL field that has no DEFAULT declared
create table foo
( id integer primary key
, a varchar(20) not null default "i dunno"
, b integer not null
, c integer default 21
)
you can leave the column list off if you provide a value for every column in their exact order
insert into foo
values (1, 'it''s me', 641, 20)
but you can also do it by inserting only some of the values, letting the other ones default
insert into foo (b, id, c)
values (641, 2, null)
rudy
well, some autonumbers you leave out (sql/server), some you have to mention (oracle), and then there's mysql, where it's up to you, you can stick anything into it, including a null or a number like 3,456,937...
my point was, you could leave other columns out if they have a decent default
a great example is a "date added" column -- many people feed the current date into a row from the external scripting language, whereas the easiest way to do it is to declare
dateadded datetime not null default getdate()
when you create the table, and then forget about this column altogether in any INSERT statements
rudy
my point was, you could leave other columns out if they have a decent default
a great example is a "date added" column -- many people feed the current date into a row from the external scripting language, whereas the easiest way to do it is to declare
dateadded datetime not null default getdate()
when you create the table, and then forget about this column altogether in any INSERT statements
rudy
- Status