Collections:
Providing Column Names in INSERT Statements in SQL Server
How to provide column names in INSERT Statements in SQL Server?
✍: FYIcenter.com
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:
The following tutorial exercise gives you some good examples:
INSERT INTO fyi_links (url, id) VALUES ('sqa.fyicenter.com', 103) GO (1 row(s) affected) INSERT INTO fyi_links (id) VALUES (110) GO Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'url', table 'FyiCenterData.dbo.fyi_links'; column does not allow nulls. INSERT fails. The statement has been terminated. SELECT * FROM fyi_links GO id url notes counts created 101 dev.fyicenter.com NULL 0 2006-04-30 102 dba.fyicenter.com NULL 0 2007-05-19 103 dba.fyicenter.com NULL NULL 2007-05-19
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.
⇒ Duplicate Key Error on Primary Key Columns in SQL Server
⇐ DEFAULT - Using Column Default Values in INSERT Statements in SQL Server
2016-11-02, 1305🔥, 0💬
Popular Posts:
How to change the data type of an existing column with "ALTER TABLE" statements in SQL Server? Somet...
Where to find answers to frequently asked questions on CREATE, ALTER and DROP Statements in MySQL? H...
How To Change the Password for Your Own User Account in MySQL? If you want to change the password of...
What Are Bitwise Operations in SQL Server Transact-SQL? Bitwise operations are binary operations per...
How To Connect the Oracle Server as SYSDBA in Oracle? This is Step 4. The best way to connect to the...