I've always used such script to insert data into a table in delphi 7
sql := 'INSERT INTO table_foo (field1,field2,field3) VALUES ('
+quotedstr('value1')
+','+quotedstr('value2')
+','+quotedstr('value3')
+')';
adoquery1.close;
adoquery1.sql.text := sql;
adoquery1.execsql;
but one of my friend just showed me another way that looks cleaner, like so:
sql := 'SELECT * FROM table_foo';
adoquery1.close;
adoquery1.sql.text := sql;
adoquery1.open;
adoquery1.insert;
adoquery1.fieldbyname('field1').asstring := quotedstr('value1');
adoquery1.fieldbyname('field2').asstring := quotedstr('value2');
adoquery1.fieldbyname('field3').asstring := quotedstr('value3');
adoquery1.post;
which of the two methods are better (faster, easier to read/debug)? especially when the data in table_foo is large or there are a lot more fields to fill.
If you do use INSERT INTO statements use parameters (for reasons of readability, avoid SQL injection, SQL caching) e.g.:
adoquery1.sql.text := 'INSERT INTO table_foo (field1, field2) values (:field1, :field2)';
adoquery1.Parameters.ParamByName('field1').Value := value1;
adoquery1.Parameters.ParamByName('field2').Value := value2;
I prefer the second way (with a small tweak which I'll explain).
Since you are inserting one record, the tweak is to select an empty recordset i.e.:
SELECT * FROM table_foo where 1=0
This way you don't select all records form the table.
Also no need to use QuotedStr when assigning the values i.e.:
adoquery1.FieldByName('field1').AsString := 'value1';
The main reason I use this method is because it's easy to read and to maintain.
I don't need to bother myself with pure SQL queries. I don't need to deal with Parameters which sometime required to specify the data type for the parameters (e.g. Parameters.ParamByName('field1').DataType := ftInteger). No need to ParseSQL.
I simply use the DataSet As(Type) e.g.
FieldByName('field1').AsBoolean := True;
I would also prefer to use this method if I need to insert multiple records in a single transaction.
The downside for the second method is the short trip to the SQL server via SELECT FROM.
Another option would be to create a SQL stored procedure, pass your values to the SP, and write all the SQL logic inside the SP.
The second approach demands more local resources from the dataset, since it will keep a memory of the original result set and then use that memory to decide which records should be sent to the server by using which SQL statement. That approach also requires a live connection with the server and a bidirectional local cursor set in the dataset. TADODataset does all that for you. It works more to you work less, but it will consume more from the system. The decison, under my view, depends on which resource is more important, your time or computer resources.
Personaly, I prefer using TClientDataset (CDS). It will allow you to have an in-memory dataset and by using TDatasetProvider.BeforeUpdateRecord event in the corresponding TDatasetProvider you will get the best of both worlds: absolute control over which sentence will be submited to the server and a flexible and bidirectinal dataset that works very well on GUIs.
Besides (this is the most important to me), with CDS you will be able to isolate the specifics of your DBMS away from the main logic of your application, because that logic will be operating on a DB-independent dataset. If you have to shift from ADO to, let´s say, DBX, your main code will not be hurt because it´s written on CDS.
