In this discussion, we’ll explore different ways records can be added to a table using the insert statement. The insert statement is the only statement that actually populates table with a record. Now, we saw the various constraints that can be imposed on a table in tutorial 1. This constraints must be obeyed when inserting records into the tables. That is to say, if you declare a column as the primary key, you can’t insert duplicate copies or null for that column. Unique columns can not have duplicate copies but allow one NULL in the column. When a column has a check constraint, the data being inserted must meet that constraint or it will throw an error. Any column that has a default constraint can be left out during the insertion procedure if there is no value to be inserted in it and the default value will automatically be inserted for that column. I don’t know if l talked about an identity column earlier but an identity column is an auto-increment integer column that is automatically populated by sql server. We need to provide a seed and increment value and every row is given a unique identity value.
To declare an identity column, we do it like this,
CREATE TABLE student
(sid int primary key,
st_number int identity (1,1),
firstname varchar(50),
lastname varchar(50),
tuition money default 100, check (tuition >=0)
dateofbirth date not null)
So we have this student table above, lets now try to add a record to the student table
INSERT INTO student(sid,firstname,lastname,tuition,dateofbirth)
VALUES (1,’Vincent’, ‘Amedekah’,200, ‘1900-04-04′)
The insert statement has to specify column names if you are entering data only in some specific columns. There is no need to specify column names if you are entering the data into all the columns in the order in which they exist in the table. The disadvantage of not specifying columns is that, you have to enter the data in the order in which the columns were declared during the table creation. To avoid remembering this order and just enter according to the order you wish, give the column names and enter the data in order of the column names you specified. If you specify a column and no data was provided for that column, it will be assigned NULL if there is no default value constraint added to that column, however, if a column is declared NOT NULL and there is no default value constraint on it, leaving out a value for that column during an insert will throw an error. Also note that for an identity column, you do not provide values and you don’t even specify the column name in the definition since you don’t have anything to do with it.
Another important thing to note is the single quotes for character strings and dates. if You don’t put it, you’ll get error. Assume we want to insert a record and let the tuition column of a student take the default value, we’ll do it like this,
INSERT INTO student(sid,firstname,lastname,dateofbirth)
VALUES (2,’Brittany’, ‘Spears’, ‘1900-04-04′)
Now, you can see that l didn’t put the column for tuition in the insert statement. That means l want it to assume the default value. I also did not put the st_number column because it will be automatically populated by the server. Now, if l were to leave out the dateofbirth column, it will throw an error because l declared that column as NOT NULL. That means, in every insert statement, that column must be provided a value. This is how the check constraint works, assuming in entering data for a student, and l make an error entering negative tuition like this,
INSERT INTO student(sid,firstname,lastname,dateofbirth, tuition)
VALUES (3,’Miley’, ‘Cyrus’, ‘1900-04-04′,-200)
This will throw an error because l declared the check constraint for this column as check(tuition >=0). This is very important to avoid entering bad data.
we can also enter multiple records using one insert statement as shown below
INSERT INTO student(sid,firstname,lastname,dateofbirth,tuition)
VALUES (4,’Samuel’, ‘Jackson’, ‘1900-04-04′,120),
(5,’Barack’, ‘Obama’, ‘1900-04-04’,200)
The problem with entering multiple records in a single insert statement is that, all records must obey the column names defined by the insert statement in the beginning.
Now let’s look at how we can copy data from one table to another. Assume we already have the student table populated with data and we want to copy that data to another table say student_backup table. The student_backup table must already exist in other to copy the data into it, that’s obvious but l just like to mention it because there is another statement which can create the new table as well as copy the data from the existing table into it, that’s the select into statement which l’ll be discussing in the next tutorial.
INSERT INTO student_backup
SELECT sid, st_number, firstname,lastname, tuition, dateofbirth
FROM dabasename.schema.student
we will be looking at the select statement in the next tutorial but for now, lets just understand that in the insertion, we select those columns from the student table and use it to populate student_backup table.
That’s the end of this tutorial, watch out for the use of the select statement in the next tutorial. Questions and feedbacks welcome. Just drop them in the comment box.
