INTERVIEW QUESTIONS DATABASE MS SQL SERVER DETAILS

Question: How to provide column names in INSERT Statements in MS SQL Server?

Answer: If 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

If 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 Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question