The INSERT INTO statement can be used to insert multiple rows by grouping the statement. The following SQL inserts three rows into the EmployeeDetail table in the sample database. Because values for all columns are supplied and are listed in the same order as the columns in the table, the column names do not have to be specified in the column list.
This example uses a column list to explicitly specify the values that are inserted into each column.
The column order in the EmployeeDetail table in the Sample database is EmpName, EmpAddress, EmpCity; however, the columns are not listed in that order in column_list.
Syntax
- Insert into EmployeeDetails values('PriyanK','Indrapuram,','Ghaziabd', 'Ghaziabad',9055345544, GETDATE());
Insert data into a table with columns that have default value
This statement shows inserting rows into a table with columns that automatically generate a value or have a default value. Column_1 is a computed column that automatically generates value by concatenating a string with the value inserted into column_2. Column_2 is defined with a default constraint.
If a value is not specified for this column, the default value is used.
Column_3 is defined with the rowversion data type, which automatically generates a unique, incrementing binary number.
Column_4 does not automatically generate a value. When a value for this column is not specified, NULL is inserted. The INSERT statements insert rows that contain values for some of the columns but not all. In the last INSERT statement, no columns are specified and only the default values are inserted by using the DEFAULT VALUES clause.
Syntax
- CREATE TABLE Employee
- (
- column_1 AS 'Computed column ' + column_2,
- column_2 varchar(30)
- CONSTRAINT default_name DEFAULT ('my column default'),
- column_3 rowversion,
- column_4 varchar(40) NULL
- );
- GO
- INSERT INTO Employee (column_4)
- VALUES ('Explicit value');
- INSERT INTO Employee (column_2, column_4)
- VALUES ('Explicit value', 'Explicit value');
- INSERT INTO Employee (column_2)
- VALUES ('Explicit value');
- INSERT INTO Employee DEFAULT VALUES;
- GO
- SELECT column_1, column_2, column_3, column_4
- FROM Employee;
- GO