SQL Server – DML INSERT Statement

The very basic question a person asks, when he starts learning databases is – How can I insert a data in a table using T-SQL?

To insert the data into a table we use INSERT statement. There are four different forms of INSERT statement. We shall look at all the variations one-by-one.

For demo purpose we shall use a simple Customer table:

Customer Table

• INSERT / VALUES

It is the most simplest and direct method of inserting data. Inserts a single row of values at a time; commonly used to insert data from a user interface.
When data for all the columns need to be entered, we can do it as follows:

INSERT INTO Customer
VALUES (111, ‘Williams’, ‘James‘, ‘NY’, ‘1234578414′);

When data for the selected columns has to be inserted, then we need to specify the column names of the table:

INSERT INTO Customer (CustID, LastName, Phone)
VALUES (112, ‘Krishnan’, ‘514586245′);

The column need not be in the same sequence as in the table definition. We can also change the order of the column:

INSERT INTO Customer (Address, LastName, CustID)
VALUES (‘LA’, ‘Anderson’, 113);

We can see how our table looks after executing above INSERT statements:

SELECT * FROM Customer;

Customer Table

• INSERT / SELECT

It is used to insert the result set of another select statement. If we want to copy records from some legacy table or data from any third party, we can use these INSERT form to copy all or selected records at one go. There is no limitation to number of records we can insert. The only consideration that has to be taken care of is that the data types must be compatible.

– Inserting records into Customer table form
– Europe_Customer table

INSERT INTO Customer (CustID, LastName, FirstName)
SELECT CID, LName, Fname FROM Europe_Customer;

• INSERT / EXEC

This from of INSERT statements pulls data from the stored procedure and inserts it into a table. Generally used for complex data manipulation

– Inserting records into Cutomer table returned
– by the stored procedure USP_NewCustomers

INSERT INTO Customer
EXEC USP_NewCustomers;

• INSERT DEFAULT

This is special form of INSERT statement, which is rarely used in the real world. It creates a new row with all defaults. This form only accepts table name and no other parameters.

INSERT Customer DEFAULT VALUES;

• SELECT INTO

Don’t get confused by the name. It is a form of INSERT statement which creates a new table. The result set of a select statement is use to populate the new table whose name is specified in the statement.

– A new table CustomerAddress is created with
– CustID and Address columns

SELECT CustID, Address
INTO CustomerAddress
FROM Customer

Good Luck!