While inserting rows into a table, Database developer need to consider the following guidelines:
Consider an example of the Address table that is used to store addresses of the employees. The following table describes the structure of the Address table.
AddressID int NOT NULL
AddressLine1 nvarchar(60) NOT NULL
AddressLine2 nvarchar(60) NULL
StateProvinceID int NOT NULL
PostalCode nvarchar(15) NOT NULL
To insert a row into the Address table with all the column values, you can use any one of the following statements:
INSERT into Address
VALUES (104, ’24, Herbon Aptsi, ‘Arthor Lane’, 56, ‘607009’)
Or
INSERT into Address (AddressID, AddressLIne1, AddressLine2, StateProvinceID, PostalCode)
VALUES (104, ’24, Herbon Apts’, ‘Arthor Lane’, 56, ‘607009’)
Or
INSERT into Address (AddressID, AddressLine1, AddressLine2, PostalCode, StateProvinceID)
VALUES (104, ’24, Herbon Apts’, ‘Arthor Lane’, ‘60070’, 56)
Or
INSERT into Address
VALUES (104, ’24, Herbon Apts’, NULL, 56 ‘607009’)
In the previous example of Address table, the AddressLine2 column allows you to enter a NULL value in a row. Therefore, you can use the following statements to insert partial data into the table:
INSERT into Address
VALUES (104, ’24, Herbon Apts’, NULL, 56, ‘607009’)
Or
INSERT into Address (AddressID, AddressLine1, PostalCode, StateProvinceID)
VALUES (104, ’24, Herbon Apts’, ‘607009’, 56)
Manipulate data in tables
- The number of data values must be the same as the number of attributes in the table or column list.
- The order of inserting the information must be the same as the order in which attributes are listed for insertion.
- The values clause need not contain the column with the IDENTITY property.
- The data types of the information must match the data types of the columns of the table.
Consider an example of the Address table that is used to store addresses of the employees. The following table describes the structure of the Address table.
AddressID int NOT NULL
AddressLine1 nvarchar(60) NOT NULL
AddressLine2 nvarchar(60) NULL
StateProvinceID int NOT NULL
PostalCode nvarchar(15) NOT NULL
To insert a row into the Address table with all the column values, you can use any one of the following statements:
INSERT into Address
VALUES (104, ’24, Herbon Aptsi, ‘Arthor Lane’, 56, ‘607009’)
Or
INSERT into Address (AddressID, AddressLIne1, AddressLine2, StateProvinceID, PostalCode)
VALUES (104, ’24, Herbon Apts’, ‘Arthor Lane’, 56, ‘607009’)
Or
INSERT into Address (AddressID, AddressLine1, AddressLine2, PostalCode, StateProvinceID)
VALUES (104, ’24, Herbon Apts’, ‘Arthor Lane’, ‘60070’, 56)
Or
INSERT into Address
VALUES (104, ’24, Herbon Apts’, NULL, 56 ‘607009’)
Inserting Partial Data
Depending on the constraints applied to the columns of the tables, you can insert partial data into the database tables. This means that while performing an insert operation, you can insert data for selective columns in a table. It is not necessary that you have to insert values for all columns in the table. The SQL Server allows you to insert partial data for a column that allows NULL or has a default constraint assigned to it. The INSERT clause lists the columns for which data is to be inserted, except those columns that allow NULL or have a default constraint. The VALUES clause provides values for the specified columns.In the previous example of Address table, the AddressLine2 column allows you to enter a NULL value in a row. Therefore, you can use the following statements to insert partial data into the table:
INSERT into Address
VALUES (104, ’24, Herbon Apts’, NULL, 56, ‘607009’)
Or
INSERT into Address (AddressID, AddressLine1, PostalCode, StateProvinceID)
VALUES (104, ’24, Herbon Apts’, ‘607009’, 56)
Manipulate data in tables