SQL Server How to Insert Records into Table

In the previous post you have learnt how table can be created in SQL Server database. Once table is created in a database, the next task is to insert some records in the table. In this tutorial you will learn how to insert records in a table in SQL Server.

To insert records into a table, you can use GUI (Graphic User Interface) mode or can use INSERT INTO command.

1. GUI Mode

To insert row in table in GUI mode is very easy. Right click the table in which you want to insert records and click Top 200 Rows menu item. The Table Designer opens in insert mode to add rows. The snapshot is shown below.

2. SQL Script Mode

The syntax of the INSERT INTO command is as follows.

INSERT [INTO] <table name> [(columns-list)] VALUES (data-list), (data-list),

The INSERT INTO clause must be followed by the table name in which row is inserted. If data is not inserted into all columns of the table then the column list must be specified after the table name in the INSERT INTO command. After the column list, you must use VALUES keyword. After the VALUES keyword, inside the square brackets, you write the data of the columns separated by comma. The INTO keyword is optional in the INSERT INTO command. The data inserted into a column of the table can be as a constant expression or a default value or can be select statement or executable statement. You will see each of them one by one later.

Note that when you insert record into table then each data must be compatible with the data type of the column of the table in which record is inserted. In fact, there is mapping between the data types of column list and data list. The data list given in the square bracket represents a row and column-list represents table header. You can add more than one row in a table at the same time by using comma separated square brackets where each square bracket holds a data list.

Repeatedly note that in the INSERT INTO command, the column list and the data list must be in the square brackets. The column list is just after the table name and the data list is just after the VALUES keyword.

The following is an example of how you can insert more than one row at a time in the table.

Transaction Log File

Every change made into a database object e.g. table is logged into transaction log file. When you insert a row into a table or delete a record or update the row the transaction is logged into the transaction table.

Memory Resident Tables

When a row is inserted into table, it is first of all inserted into a memory resident table called inserted. Similarly, when a row is deleted from table it is first of all inserted into a memory resident table called deleted. Likewise, when a row is updated into table, the new record is inserted into the memory resident inserted table and old record is inserted into deleted table. These memory resident tables are very useful when you learn trigger or to debug the query.

The Identity column is not supplied data when you insert row in a table. SQL server automatically determines the default value and the Identity value.

Let’s consider how to insert default value in a table.

Inside data from one into another table which are of compatible columns you use select sub query with insert into command.

The select sub query is used to get the record set from the source table and insert into command is used to mentioned target table in which data will be inserted. The sub query can you join clause to combine more than one tables as a source data.

Whenever a database objects is created in the database that object is tied with owner who has created the object. The owner can put restriction on the object so that other users cannot use that object.

In the next post, you will learn how to delete rows from table in SQL Server in different ways.