SDAC

Inserting Data Into Tables

This tutorial describes how to insert data into tables using the TMSQuery and TMSTable components.

  1. Requirements
  2. General information
  3. Design Time
  4. Run Time
  5. Additional Information

Requirements

This walkthrough supposes that you know how to connect to server (tutorials "Connecting To SQL Server" and "Connecting To SQL Server Compact") and that necessary objects are already created on the server (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 TMSQuery. Another way is to use the dataset functionality (the Insert, Append, and Post methods) of the TMSQuery and TMSTable 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

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

Run time

Using DataSet Functionality

The Insert, Append, and Post methods of the TMSQuery and TMSTable components allow inserting data not using DML statements. DML statements are generated by SDAC 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
  q: TMSQuery;
begin
  q := TMSQuery.Create(nil);
  try
    q.Connection := con; // con is either TMSConnection or TMSCompactConnection already set up
    q.SQL.Text := 'SELECT * FROM dept';
    q.Open;
    q.Append;
    q.FieldByName('deptno').AsInteger := 10;
    q.FieldByName('dname').AsString := 'ACCOUNTING';
    q.FieldByName('loc').AsString := 'NEW YORK';
    q.Post;
  finally
    q.Free;
  end;
end;

[C++Builder]

{
  TMSQuery* q = new TMSQuery(NULL);
  try
  {
    q->Connection = con; // con is either TMSConnection or TMSCompactConnection already set up
    q->SQL->Text = "SELECT * FROM dept";
    q->Open();
    q->Append();
    q->FieldByName("deptno")->AsInteger = 10;
    q->FieldByName("dname")->AsString = "ACCOUNTING";
    q->FieldByName("loc")->AsString = "NEW YORK";
    q->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
  q: TMSQuery;
begin
  q := TMSQuery.Create(nil);
  try
    q.Connection := con; // con is either TMSConnection or TMSCompactConnection already set up
    q.SQL.Clear;
    q.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);');
    q.ParamByName('deptno').AsInteger := 10;
    q.ParamByName('dname').AsString := 'ACCOUNTING';
    q.ParamByName('loc').AsString := 'NEW YORK';
    q.Execute;
  finally
    q.Free;
  end;
end;

[C++Builder]

{
  TMSQuery* q = new TMSQuery(NULL);
  try
  {
    q->Connection = con; // con is either TMSConnection or TMSCompactConnection already set up
    q->SQL->Clear();
    q->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);");
    q->ParamByName("deptno")->AsInteger = 10;
    q->ParamByName("dname")->AsString = "ACCOUNTING";
    q->ParamByName("loc")->AsString = "NEW YORK";
    q->Execute();
  }
  __finally
  {
    q->Free();
  }
}

DML Statements As Plain Text

[Delphi]

var
  q: TMSQuery;
begin
  q := TMSQuery.Create(nil);
  try
    q.Connection := con; // con is either TMSConnection or TMSCompactConnection already set up
    q.SQL.Clear;
    q.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (10,''ACCOUNTING'',''NEW YORK'');');
    q.Execute;
  finally
    q.Free;
  end;
end;

[C++Builder]

{
  TMSQuery* q = new TMSQuery(NULL);
  try
  {
    q->Connection = con; // con is either TMSConnection or TMSCompactConnection already set up
    q->SQL->Clear();
    q->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK');");
    q->Execute();
  }
  __finally
  {
    q->Free();
  }
}

Additional Information

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

It is also possible to use stored procedures for inserting data. In this case, all data manipulation logic is defined on the server. You can find more about using stored procedures in the tutorial "Stored Procedures".

© 1997-2024 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback