Inserting Data Into Tables

This tutorial describes how to insert data into tables using the TLiteQuery and TLiteTable components.

Requirements

This walkthrough supposes that you know how to connect to server (tutorials "Connecting To SQLite Database") and that necessary objects are already created in the database (tutorial "Creating Database Objects").

General information

Data on server can be inserted using Data Manipulation Language (DML), which is a part of SQL. DML statements can be executed on server by an account that has necessary privileges. There are two ways to manipulate a database. You can build DML statements manually and run them within some component like TLiteQuery. Another way is to use the dataset functionality (the Insert, Append, and Post methods) of the TLiteQuery and TLiteTable components. We will discuss both ways.

The goal of this tutorial is to insert the following data into tables dept and emp:
Table dept

deptno	dname	loc
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

Table emp

ename	job	mgr	hiredate	sal	comm	deptno
SMITH	CLERK	7902	17.12.1980	800	NULL	20
ALLEN	SALESMAN	7698	20.02.1981	1600	300	30
WARD	SALESMAN	7698	22.02.1981	1250	500	30
JONES	MANAGER	7839	02.04.1981	2975	NULL	20
MARTIN	SALESMAN	7698	28.09.1981	1250	1400	30
BLAKE	MANAGER	7839	01.05.1981	2850	NULL	30
CLARK	MANAGER	7839	09.06.1981	2450	NULL	10
SCOTT	ANALYST	7566	13.07.1987	3000	NULL	20
KING	PRESIDENT	NULL	17.11.1981	5000	NULL	10
TURNER	SALESMAN	7698	08.09.1981	1500	0	30
ADAMS	CLERK	7788	13.07.1987	1100	NULL	20
JAMES	CLERK	7698	03.12.1981	950	NULL	30
FORD	ANALYST	7566	03.12.1981	3000	NULL	20
MILLER	CLERK	7782	23.01.1982	1300	NULL	10

Note: The empno field of the emp table is an IDENTITY(1,1) (i.e. autoincrement) field, so its value is filled automatically by the server.

Design time

Open the Component palette and find the TLiteQuery component in the LiteDAC category.
Double-click on the component. Note that a new object appears on the form. If this is the first time you create TLiteQuery in this application, it is named MSQuery1. Note that the LiteQuery1.Connection property is already set to an existent (on the form) connection.
Double-click on the MSQuery1 object.

Type the following lines:

INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');

Press the Execute button.

Performing these steps adds a new record to the dept table.

Run time

Using DataSet Functionality

The Insert, Append, and Post methods of the TLiteQuery and TLiteTable components allow inserting data not using DML statements. DML statements are generated by LiteDAC components internally. The difference between the Append and Insert methods is that Append creates a new empty record in the end of a dataset, when Insert creates it in the position of the current record of a dataset. The code below demonstrates using these methods:

[Delphi]

var
  LiteQuery: TLiteQuery;
begin
  LiteQuery := TLiteQuery.Create(nil);
  try
    // LiteConnection is either TLiteConnection already set up
    LiteQuery.Connection := LiteConnection;

    // retrieve data
    LiteQuery.SQL.Text := 'SELECT * FROM dept';
    LiteQuery.Open;

    // append record
    LiteQuery.Append;
    LiteQuery.FieldByName('deptno').AsInteger := 10;
    LiteQuery.FieldByName('dname').AsString := 'ACCOUNTING';
    LiteQuery.FieldByName('loc').AsString := 'NEW YORK';
    LiteQuery.Post;

    // insert record
    LiteQuery.Insert;
    LiteQuery.FieldByName('deptno').AsInteger := 20;
    LiteQuery.FieldByName('dname').AsString := 'RESEARCH';
    LiteQuery.FieldByName('loc').AsString := 'DALLAS';
    LiteQuery.Post;

  finally
    LiteQuery.Free;
  end;
end;

[C++Builder]

{
  TLiteQuery* LiteQuery = new TLiteQuery(NULL);
  try
  {
    // LiteConnection is either TLiteConnection already set up
    LiteQuery->Connection = LiteConnection;

    // retrieve data
    LiteQuery->SQL->Text = "SELECT * FROM dept";
    LiteQuery->Open();

    // append record
    LiteQuery->Append();
    LiteQuery->FieldByName("deptno")->AsInteger = 10;
    LiteQuery->FieldByName("dname")->AsString = "ACCOUNTING";
    LiteQuery->FieldByName("loc")->AsString = "NEW YORK";
    LiteQuery->Post();

    // insert record
    LiteQuery->Insert();
    LiteQuery->FieldByName("deptno")->AsInteger = 20;
    LiteQuery->FieldByName("dname")->AsString = "RESEARCH";
    LiteQuery->FieldByName("loc")->AsString = "DALLAS";
    LiteQuery->Post();

  }
  __finally
  {
    q->Free();
  }
}

Building DML Statements Manually

DML Statements can contain plain text and text with parameters. This section describes both ways.

DML Statements With Parameters

[Delphi]

var
  LiteQuery: TLiteQuery;
begin
  LiteQuery := TLiteQuery.Create(nil);
  try
    // LiteConnection is either TLiteConnection already set up
    LiteQuery.Connection := LiteConnection;

    // set SQL query for insert record
    LiteQuery.SQL.Clear;
    LiteQuery.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);');
    
    // set parameters
    LiteQuery.ParamByName('deptno').AsInteger := 10;
    LiteQuery.ParamByName('dname').AsString := 'ACCOUNTING';
    LiteQuery.ParamByName('loc').AsString := 'NEW YORK';

    // execute query
    LiteQuery.Execute;
  finally
    LiteQuery.Free;
  end;
end;

[C++Builder]

{
  TLiteQuery* LiteQuery = new TLiteQuery(NULL);
  try
  {
    // LiteConnection is either TLiteConnection already set up
    LiteQuery->Connection = LiteConnection;

    // set SQL query for insert record
    LiteQuery->SQL->Clear();
    LiteQuery->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);");

    // set parameters
    LiteQuery->ParamByName("deptno")->AsInteger = 10;
    LiteQuery->ParamByName("dname")->AsString = "ACCOUNTING";
    LiteQuery->ParamByName("loc")->AsString = "NEW YORK";

    // execute query
    LiteQuery->Execute();
  }
  __finally
  {
    LiteQuery->Free();
  }
}

DML Statements As Plain Text

[Delphi]

vvar
  LiteQuery: TLiteQuery;
begin
  LiteQuery := TLiteQuery.Create(nil);
  try
    // LiteConnection is either TLiteConnection already set up
    LiteQuery.Connection := LiteConnection; 

    // set SQL query for insert record
    LiteQuery.SQL.Clear;
    LiteQuery.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (10,''ACCOUNTING'',''NEW YORK'');');

    // execute query
    LiteQuery.Execute;
  finally
    LiteQuery.Free;
  end;
end;

[C++Builder]

{
  TLiteQuery* LiteQuery = new TLiteQuery(NULL);
  try
  {
    // LiteConnection is either TLiteConnection already set up
    LiteQuery->Connection = LiteConnection; // con is either TLiteConnection already set up

    // set SQL query for insert record
    LiteQuery->SQL->Clear();
    LiteQuery->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK');");

    // execute query
    LiteQuery->Execute();
  }
  __finally
  {
    LiteQuery->Free();
  }
}

Additional Information

Actually, there are lots of ways to insert data into tables. Any tool or component that is capable of running a SQL query, can be used to manage data. Some components are best for performing certain tasks. For example, TLiteLoader is the fastest way to insert data, TLiteScript is designed for executing series of statements one by one.

Request Support

DAC Forum

Provide Feedback