Aug 12, 2013

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.


Suppose we have created a table tblOrder and inserted some data into it in sql server

CREATE TABLE tblOrder(
     OrderId INT PRIMARY KEY,
     OrderName VARCHAR(100)
)

INSERT tblOrder VALUES(1,'PC'),(2,'Laptop')

There is another table tblOrderHistory:

CREATE TABLE tblOrderHistory(
     OrderId INT,
     OrderName VARCHAR(100),
     HistoryDate DATETIME DEFAULT(GETDATE())
)

Now if we will execute following sql query:

INSERT INTO tblOrderHistory(OrderId,OrderName,HistoryDate)
SELECT * FROM tblOrder

We will get error message like:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

Cause: Select statement from tblOrder selecting only two columns OrderId and OrderName while we are trying to insert in the three columns. So it is syntax error.

Solution:

Correct syntax is:

INSERT INTO tblOrderHistory(OrderId,OrderName)
SELECT * FROM tblOrder

No comments:

Post a Comment