databasedev.co.uk - database solutions and downloads for microsoft access

Data Manipulation Language Commands

INSERTing Data using SQL:

The Data Manipulation Language aspect of SQL (Structured Query Language) refers to the commands and functions of the language designed to modify the data in the database. The database structures remain unchanged, while the data held within is updated, deleted or inserted. The selection of data, via a query, is also part of the DML architecture.

SQL INSERT, UPDATE and DELETE are the Updating commands of the DML, while SELECT is the general Querying command. The INSERT statement will be dealt with here, but we will in future articles in this series explore the usage of UPDATE, DELETE and the wide-ranging SELECT command.

Updating the Database: SQL INSERT, UPDATE and DELETE

SecA_Bal

Name Customer# Agent Balance
John Smith 2593 Brown 121.50
Neale Fenn 2529 Brown -42.00
Kenny Perry 2511 Dowling 553.50
Jim Croker 1311 Brown 0

SQL INSERT

This command adds new rows of data to the database. The format used is:

INSERT INTO SecA_Bal 
VALUES ('Mark Yeates', '1501', 'Dowling', '383.81');

This creates a new row, updating the table as follows:

Name Customer# Agent Balance
John Smith 2593 Brown 121.50
Neale Fenn 2529 Brown -42.00
Kenny Perry 2511 Dowling 553.50
Jim Croker 1311 Brown 0
Mark Yeates 1501 Dowling 383.81

The INSERT statement can be used with a variety of other commands to update data in various ways.

If we wish to insert data for a new row only into specific columns, we must specify which columns we want updated:

INSERT INTO SecA_Bal (Name, Balance)
VALUES ('Mark Yeates', '383.81');

This would create the Mark Yeates column, leaving the 'Customer #' and 'Agent' columns empty.

INSERT can also be used to copy multiple rows from one table to another, in conjunction with the SELECT statement. The format for performing this is:

INSERT INTO table_name, (columns)
SELECT ….

The usage of SELECT with INSERT, UPDATE or DELETE makes it much easier to make a variety of changes at one time. SELECT performs a query, and using this command as a sub-command of a modifying SQL command allows a change to all results of that query at once. This will be dealt with in future articles.

Readers of this article may also like to check out other similar articles on this subject: