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

Feb 232011

I ran into the error: 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.
Well I’ve run into it often.

Normally this simply means you have specified fewer items in values for an INSERT statement than you have specified you need. For example:

INSERT INTO Table1
( ID, [Column], Column1 )
SELECT 1,
'Value',

Now, that’s easily rectified, by including the columns you need or removing the one from INSERT you do not need.

However, I also noticed that in my situation I had all the columns needed, but I was lacking a comma – like this:

INSERT INTO Table1
( ID, [Column], Column1 )
SELECT 1,
'Value1'
'Value2'

Notice the lack of comma between Value1 and Value2.

This gives the mentioned error and not a syntax error in the SELECT statement. The reason is that SQL Server concatenates the Value1 and Value2 fields into one column, and therefore the SELECT only produces 2 columns.
SQL Server will be able to concatenate two columns into one without needing to add them together, but only two columns.
So if I were to forget the third comma I would get an “Incorrect syntax near Value2″ error message alerting me to the issue. But forget one comma between 2 columns and you get no such information.

I didn’t see a mention of this in the MSDN documentation.

The MERGE syntax - syntax and performance

May 212010

Normally when you have rows from one table you want to move over into the other, you’ll have to run both an update query to get your existing rows updated with the new values and insert query to get new rows over to your target. That means you have to write two queries. With the merge, you only need one as it performs both the update and the insert.
So let’s take a look at it.

First, I’ll create 2 tables with dummy data; a tblSource and a tblTarget.
The scripts for creating the tables and data can be found here merge_setup_20100521.sql (3.58 kb), but basically it’s just a target and source table with an ID, ValA, ValB and ValC with a clustered index on ID and some dummy data.
So to do the update/insert it would look like this:
UPDATE MyTarget SET
MyTarget.ValA = MySource.ValA,
MyTarget.ValB = MySource.ValB,
MyTarget.ValC = MySource.ValC
FROM dbo.tblTargetInUp AS MyTarget
INNER JOIN dbo.tblSourceInUp AS MySource ON MyTarget.ID = MySource.ID

INSERT INTO dbo.tblTargetInUp
SELECT ID, ValA, ValB, ValC
FROM dbo.tblSourceInUp AS MySource
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblTargetInUp AS MyTarget WHERE MyTarget.ID = MySource.ID)

If we look at the execution plan, I get an estimated cost of 9.1754 for the update and 2.48725 for the insert, meaning a combined cost of 11.66265 for 33.333 rows updated and 33.3334 rows inserted, into a target of 66.666 rows.

Now do remember, the estimated cost is just a number for how the query runs in my environment, it can’t be taken as a direct number and transferred to another system – I’m only interested in the relative comparison with the merge. More data, more indexes, more variations will all affect the actual numbers. Also if looking at the execution plan, it is clear it is two queries we fire, meaning that all the overhead which goes into running one query will be doubled for this. But it was how you’d have to do merges in the past.

Now, with the merge syntax we can do it like this:
MERGE dbo.TblTargetMerge AS MyTarget USING
(
SELECT * FROM dbo.TblSourceMerge AS TS
) AS MySource
ON MyTarget.ID = MySource.ID
WHEN MATCHED THEN
UPDATE SET
MyTarget.ValA = MySource.ValA,
MyTarget.ValB = MySource.ValB,
MyTarget.ValC = MySource.ValC
WHEN NOT MATCHED THEN
INSERT
(ID, ValA, ValB, ValC)
VALUES
(MySource.ID, MySource.ValA, MySource.ValB, MySource.ValC)
;

Note that the syntax takes both the update and insert in the WHEN MATCHED and WHEN NOT MATCHED.
For the complete overview over the syntax, I’ll refer you to the documentation by microsoft: MERGE (Transact-SQL).
But basically – you MERGE into a table using a source, and then define the ON clause (as you would a join), and then specify the WHEN MATCHED and the WHEN NOT MATCHED clauses.

One thing I’ll expand on myself though, is the OUTPUT clause which can also be coupled on to the merge. I mentioned the OUTPUT clause myself recently.
The important thing is that you can couple the $action to the output clause and get information about whether you merged the data or you inserted the data, meaning whether the row was matched or not matched.
Like this:

MERGE dbo.TblTargetMerge AS MyTarget USING
<…snipped FOR being brief…>
OUTPUT $ACTION, rest_of_select_here
;

Nifty.
Anyways – once we’ve build this query, we can look at the execution plan, and here we clearly see it is handled as one query.
And in my case, the estimated cost is 8.407 for the same number of rows as above, meaning we’ve saved about 27.9% just by changing syntax.

Now, this structure I used to compare is very simple – with a simple matching of ID to ID and then just insert/overwrite everything so the actual result may vary (naturally), however the merge syntax does appear to be faster and with the added bonus of keeping the query combined into one syntax rather than divided into two different queries.

I must admit, I do like the merge syntax myself once I learned to read and write them.

The OUTPUT clause

Mar 202010

The output clause is a really nifty thing in SQL Server 2008, which provides you access to the same “inserted” and “deleted” tables you get access to via triggers, but from within the same query.
To illustrate, suppose we have a table:

CREATE TABLE [dbo].[OutputEx](
[Key] [int] IDENTITY(1,1) NOT NULL,
[Value] [nchar](10) NULL,
[SecondValue] [datetime2](7) NULL,
CONSTRAINT [PK_OutputEx] PRIMARY KEY CLUSTERED
(
[Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[OutputEx] ADD  CONSTRAINT [DF_OutputEx_SecondValue] DEFAULT (GETDATE()) FOR [SecondValue]

This is a table which has an identity column in [Key] and a datetime value field in SecondValue2 which defaults to the current date (GETDATE())

Now, if I insert a [Value] into this table SQL Server will automatically insert the identity value and the current date SecondValue, but if I wanted to get these automated values out, I would have had to select them out manually afterwards.
This might not seem like much of a problem given these two values (Identity and date) however it might be much more complex constraints and default values.

So what I can do is use the OUTPUT clause like this:

INSERT INTO OutputEx ( Value)
OUTPUT inserted.[key], inserted.secondvalue
VALUES ( 'a')

This will provide me with a result of the identity value inserted into [key] and the date value inserted into SecondValue.
I can use this result in an outer query by nesting the insert in a sub-query, or I can insert it into another table directly like this:

DECLARE @T TABLE(ID INT, [Date] DATETIME2)

INSERT INTO OutputEx ( Value)
OUTPUT inserted.[key], inserted.secondvalue INTO @T
VALUES ( ‘a’)

My table variable @T will now hold the values inserted
This also works with the “deleted” table instead of the inserted and thus both in insert/update and deleted queries.
It is a useful technique.

An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON.

Sep 012008

If you run into the following error message:
An explicit value for the identity column in table ‘<TABLE_NAME>’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
It can mean two things.

One you’ve not enabled identity insert on your table, meaning SQL Server will not let you insert into the Identity column.
This can be rectified with the following statement:
SET IDENTITY_INSERT table_name ON
And then turn it off again when done
SET IDENTITY_INSERT table_name OFF

However it can also mean that you are using for example INSERT INTO, in which cause the message tells you to specify the column names. This means using the following syntax:

INSERT INTO target_able_name (column_name1, column_name2…. column_nameN)
SELECT
YOUR_SELECT_LIST_WHICH_MATCHES_COLUMN_LIST
FROM source_table_name

I wasn’t aware of the latter syntax myself before I got the error message today. But then I learned a little something new today after all :)

UPDATE/INSERT syntax trick

Jun 082008

A little trick you can use with an update or insert is an automatic assignment.
For example at times I’ve had to update all rows in a (temporay) table
with an consecutive number and a fast way of doing this is utilizing
this technique:

DECLARE @I INT

SET @I = 0

UPDATE Table_1

SET @I = MyID = @I + 1

This example instantiates an interger (I) to the value 0. Then I
update a table where I set an ID column to the value of I + 1, but at
the same time I set my I variable to the value of MyID. This means that
the first row will get the value 1 into MyID and the second will get 2
and so forth.

And of course this example is very simple, and in such a case one
might as well just use identity. However because you can make any
calculations you want, and because it does not have to be an integer
data type – you can use this for various situations.

It just goes to show that T-SQL syntax allows for playing around.