best way to insert data using dephi in sql server 2008

Question

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.

Answer 1

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.