Aug 6, 2013

The select list for the INSERT statement contains more 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 tblOrder(OrderId,OrderName)
SELECT * FROM tblOrderHistory

We will get error message like:

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

Cause: Select statement from tblOrderHistory selecting three columns while we are trying to insert into the only two columns. So it is syntax error.

Solution:

Correct syntax is:

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

No comments:

Post a Comment