Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Providing Column Names in INSERT Statements

By: FYIcenter.com

(Continued from previous topic...)

How to provide column names in INSERT Statements?

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 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.

(Continued on next topic...)

  1. What Are DML (Data Manipulation Language) Statements?
  2. How To Create a Testing Table with Test Data?
  3. How To Insert a New Row into a Table with "INSERT INTO" Statements?
  4. How To Use Column Default Values in INSERT Statements?
  5. How to provide column names in INSERT Statements?
  6. What Happens If You Insert a Duplicate Key for the Primary Key Column?
  7. How To Insert Multiple Rows with One INSERT Statement?
  8. How To Update Values in a Table with UPDATE Statements?
  9. How To Update Multiple Rows with One UPDATE Statement?
  10. How to use old values to define new values in UPDATE statements?
  11. Is the Order of Columns in the SET Clause Important?
  12. How To Use Values from Other Tables in UPDATE Statements?
  13. What Happens If the UPDATE Subquery Returns No Rows?
  14. What Happens If the UPDATE Subquery Returns Multiple Rows?
  15. How To Delete an Existing Row with DELETE Statements?
  16. How To Delete Multiple Rows with One DELETE Statement?
  17. How To Delete All Rows with DELETE Statements?
  18. How To Delete All Rows with TRUNCATE TABLE Statement?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...