How to Write a SQL Insert Statement
Introduction to SQL
SQL, also known as Structured Query Language, is a language specifically designed to interact with databases. SQL commands are inserted into programming code to manipulate databases either for retrieval, storage or maintenance.
It is based on a computer science model known as relational algebra, with simple statements consisting of logic constructs and identifiers.
SQL is widely used across databases; in fact, even a database does not use SQL for data manipulation, the database language is some form of SQL. Therefore it is a valuable skill to understand the rudiments of SQL, as it paves the ability to interact with practically any database.
The INSERT INTO Statement
The INSERT INTO statement has a standard form and syntax. The first step to writing an SQL insert statement is to understand each part of the syntax carefully. In its most basic form, the syntax is as follows:
INSERT INTO (, [, … ]) VALUES (, [, …])
In the statement’s form, ‘INSERT INTO’ and ‘VALUES’ are mandatory keywords. The parameters that have to be passed to the statement are illustrated using the <> notation. Therefore an example of usage would be:
INSERT INTO students (f_name, s_name, age) VALUES (‘Adam’, ‘Smith’,16**);**
The statement specifies exactly which columns receive the corresponding values. If the column names are not mentioned, the values will be placed in the first three columns. This is not good programming practice, although it is a shorthand version of the statement.
The data types of the values must match the ones of the columns, otherwise an error will be created.
SQL commands follow a simple structure, similar in concept to functions in programming languages. The keywords are like function names, the parameters are similar to values passed to the function, and there is always a return value – which can be null. As explained above, once the parameters are understood carefully, the statements can be made more complex to suit the requirement at hand.
Nesting statements is a process by which a complex SQL statement is created to perform more than one database operation in one statement. This is an useful programming trick, especially since it eliminates the need to store the results of one statement before passing them again as parameters to the next statement.
For example, if a record needed to be copied from one database to another, the following nested statement would accomplish the job:
INSERT INTO students
SELECT f_name, s_name, age FROM applicantdb WHERE f_name = ‘Adam’ AND s_name = ’Smith’
As the SELECT statement returns a database row, the VALUES keyword is omitted entirely from the statement. There are a number of qualifiers that can be used within statements to refine the results. ‘WHERE’ is a keyword indicating there is a condition that the results need to meet before they are accepted as valid results.
Insert statements can also be nested within other SQL commands, however the eventualities for them to be nested within other statements is few and far between.