How to provide column names in INSERT Statements in MS SQL Server?
Submitted by: AdministratorIf you don't want to specify values for columns that have default values, or you want to specify values to columns in an order different than how they are defined, you can provide a column list in the INSERT statement. If a column is omitted in the column, SQL Server applies 3 rules:
* If default value is defined for the column, that default value will be used.
* If no default value is defined for the column and NULL is allowed, NULL will be used.
* If no default value is defined for the column and NULL is not allowed, SQL Server will reject the insert statement with an error.
The following tutorial exercise gives you some good examples:
INSERT INTO ggl_links (url, id)
VALUES ('www.globalguideline.com',103)
GO
(1 row(s) affected)
INSERT INTO ggl_links (id) VALUES (110)
GO
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'url',
table 'GlobalGuideLineDatabase.dbo.ggl_links'; column does
not allow nulls. INSERT fails.
The statement has been terminated.
SELECT * FROM ggl_links
GO
id url notes counts created
101 www.globalguideline.com NULL 0 2006-04-30
102 www.globalguideline.com NULL 0 2007-05-19
103 www.globalguideline.com NULL NULL 2007-05-19
Submitted by: Administrator
* If default value is defined for the column, that default value will be used.
* If no default value is defined for the column and NULL is allowed, NULL will be used.
* If no default value is defined for the column and NULL is not allowed, SQL Server will reject the insert statement with an error.
The following tutorial exercise gives you some good examples:
INSERT INTO ggl_links (url, id)
VALUES ('www.globalguideline.com',103)
GO
(1 row(s) affected)
INSERT INTO ggl_links (id) VALUES (110)
GO
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'url',
table 'GlobalGuideLineDatabase.dbo.ggl_links'; column does
not allow nulls. INSERT fails.
The statement has been terminated.
SELECT * FROM ggl_links
GO
id url notes counts created
101 www.globalguideline.com NULL 0 2006-04-30
102 www.globalguideline.com NULL 0 2007-05-19
103 www.globalguideline.com NULL NULL 2007-05-19
Submitted by: Administrator
The first INSERT statement shows that: the order of the columns is reversed; the default value is taken for the un-specified column "created"; the NULL value is taken for the un-specified column "counts", since is has no default value defined and null is allowed.
The second INSERT statement shows the error you get for the un-specified column "url", because it has no default value, and null is not allowed.
Submitted by: Administrator
The second INSERT statement shows the error you get for the un-specified column "url", because it has no default value, and null is not allowed.
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | What Happens If NULL Values Are Involved in Arithmetic Operations? |
☺ | How To Test ODBC DSN Connection Settings? |
☺ | How To Concatenate Two Binary Strings Together? |
☺ | How To Create a View on an Existing Table in MS SQL Server? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |