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.

Selecting latest order

Oct 312009

A common problem to solve is to list, for example the current price for a product or latest order for a customer or similar.
It is a problem which quickly can seem complex, but once understanding the situation, then – as everything else – it is relative simple, and I’ll show how using both window functions (in this instance, RANK) from SQL Server 2005+ and using a sub-query in case working on an earlier version, or other databases.

I’ll take offset in the Northwind database, which I’ve installed on a SQL Server 2008.
It has an Order table with a foreign key to a Customer table; however for this the Order table is the only one of interest:
The Order table contains the following information:
SELECT [OrderID]
,[CustomerID]
,[EmployeeID]
,[OrderDate]
,[RequiredDate]
,[ShippedDate]
,[ShipVia]
,[Freight]
,[ShipName]
,[ShipAddress]
,[ShipCity]
,[ShipRegion]
,[ShipPostalCode]
,[ShipCountry]
FROM [Northwind].[dbo].[Orders]
In this situation, we’ll get the latest order per customer, but the problem is the same as if you’d need the current price – the only thing which differs will be the tables and where clauses and so on.
Solving the issue using the RANK window function, and a common table expression, it would look like this:

;WITH CTE AS (
SELECT
RANK() OVER (Partition BY T1.CustomerID ORDER BY OrderDate DESC) AS OrderRank,
T1.*
FROM Orders T1
)
SELECT * FROM CTE WHERE OrderRank = 1
ORDER BY CustomerID

What happens here is that we use RANK to give us a number partitioned (grouped) by CustomerID and sorted by OrderDate descending. This will give each row selected the number/rank that order is historically, from newest to earliest. That means each row which have the rank 1 will be the latest/newest order.
You can then expand on the joins inside/outside the common table expression to get information about customers or order details or what not.

It is also solvable without using the window function RANK, and would look for example something like this:

SELECT *
FROM Orders T1
INNER JOIN (
SELECT CustomerID, MAX(OrderDate) AS OrderDate
FROM Orders T2
GROUP BY T2.CustomerID
) T3 ON T3.OrderDate = T1.OrderDate AND T1.CustomerID = T3.CustomerID
ORDER BY T1.CustomerID

Here we utilize a sub-query where we select the CustomerID and the highest order date from Orders, which we then can join into Orders again with a self-join on customer id and order date.

Note that these are just examples. Many similar solutions exists, but they all follow the same methodology.
I’ve also not taken optimizaiton or anything like that into account. It was just to illustrate a solution to a common problem.

Difference in assignment using SELECT and SET

Sep 272008

Basically – there are two ways (at least?) of selecting information into a variable from a table/select query.

You can either SELECT @Variable = COLUMN FROM TABLE or you can SET @Variable = (SELECT COLUMN FROM TABLE).

There is one big difference between the two one needs to be aware of. The first method will not set a new value to @Variable if there isn’t returned a row from the select statement – but the second one will.

This is easy to see by running a query like this where the SELECT will return no rows:

DECLARE @Value INT
DECLARE @i INT
SET @i = 0

WHILE @i < 100 BEGIN
SELECT @Value = MyID FROM Table_1 WHERE MyValue IS NULL
IF @Value IS NULL
SET @Value = @i
PRINT(@Value)
SET @i = @i + 1
END

SET @i = 0
SET @Value = 0

Now the first impression will be that the values 0 to 99 will be printed out, but that is wrong. You’ll get 100 prints of 0. That is because the SELECT doesn’t return any rows so the assignment doesn’t happen, because it occurs within the SELECT statement.

Whereas if you change the query to this:

WHILE @i < 100 BEGIN
SET @Value = (SELECT MyID FROM Table_1 WHERE MyValue IS NULL)
IF @Value IS NULL
SET @Value = @i
PRINT(@Value)
SET @i = @i + 1
END

You’ll get the expected assignment of 0 to 99 when run, because the SELECT doesn’t return a row, the SET will assign NULL. A big difference in the execution of the assignment.

SELECT COUNT Behaviour

Apr 172008

Saw this blog piece over at Conor Cunningham’s Blog and wanted to point to it, because I never knew this behaviour.


SELECT COUNT(*) returns the number of rows in a table, but when you do SELECT COUNT(<Column>) it will return the non-null number of rows. Nifty – but behaviour which is immensely important to actually know.

The SELECT 1 vs. SELECT * in EXISTS conondrum

Feb 282008

Recently I’ve been reading some various other blogs about this issue.
Is SELECT 1 faster (better) then SELECT * when used in an EXISTS (or similar) clause?
These are two blogs I often read which discuss the issue as well:
http://blog.sqlauthority.com/2008/02/26/sql-server-select-1-vs-select-an-interesting-observation/
and
http://www.sqlskills.com/blogs/conor/2008/02/07/EXISTSSubqueriesSELECT1VsSELECT.aspx

My own personal testing have not shown any difference between the two and my “guesstimate” is that the query optimizer in the database is clever enough to judge from the syntax that it doesn’t actually need to return anything when the SELECT * is in an EXISTS, and thus it doesn’t do much else then evaluate whether the statement is true, just as if it was SELECT 1.

I’ve looked at the query plans, just as P. Dave did (the first link), and my findings were exactly the same. No difference in execution at all.

Then I tried to use the SET STATISTICS TIME ON and SET STATISTICS IO ON combined with DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE to clear the cache. This way I could see the time it took to handle the query and no result was conclusive.
The result varied from one case to the other case, so my own testing seems to conclude that IF there is a difference, it looks to be minor.
Granted, I used only a small number of columns to test with, ranging from 2 up to 20, with between 10.000 and 100.000 rows, so perhaps for very large sets, it would be different.

If anybody have any code which consistently shows that SELECT 1 is faster, it’d very much like to see it, because it is one of those SQL myths out there which are hard to prove/disprove.

Dynamic ASC and DESC and Order by DateTime

Dec 072007

Occasionally I have the problem that I need to run as query with a form of dynamic sorting – meaning sometimes ascending or descending and sometimes based on an ordernumber or a date, for example.

Now a CASE statement in the order by is often very effective for handeling such issues, but I’ve not found it possible to combine it with the Ascending and Descending clasuses (yet, could be that I’m just doing it wrong).

For example suppose we have a table which contains a “Date” field of the type DateTime, and we need to be able to sort this both descending and ascending based on some input parameter. Instead of making two queries, one might think that something like this would work

DECLARE @ASC BIT
SET @ASC = 1

SELECT *
FROM DateTimeOrderTest
ORDER BY DATE
CASE @ASC
WHEN 1 THEN ASC
ELSE DESC
END

But well – it doesn’t: “Incorrect syntax near the keyword ‘CASE’.”

Or one might think:

DECLARE @ASC BIT
SET @ASC = 1

SELECT *
FROM DateTimeOrderTest
ORDER BY
CASE @ASC
WHEN 1 THEN Date ASC
ELSE Date DESC
END

Which gives a nice little: Incorrect syntax near the keyword ‘ASC’

A little fix to get around this would be to not utilize case, but instead make the Ascending and Descending a factor so when Ascending you have a factor of 1 and Descending you have a factor of -1. Then simply convert/cast the Date to an integer (which should not be a terrible expensive procedure, as the datetime is represented as a number anyway, I wonder if it is possible to somehow use the internal representation for my purpose, or if the database does that when casting as integer….oh, well), and multiply the factor to the order by.

This mean the following two queries give the same result:

SELECT  *
FROM DateTimeOrderTest
ORDER BY CAST(Date AS INT) * -1
SELECT *
FROM DateTimeOrderTest
ORDER BY Date DESC

This technique of multiplying either 1 or -1, can be used in many queries, and of course is not limited to datetimes.

Anyways, I’ve found it quite useful myself.

SQL Fun: JOIN's

Sep 262007

Having started to get better at SQL I’m looking at ways to optimize, change and simply parts of my own methodologies when I use SQL.  This is from MS SQL 2003/2005, as it is what I use most, but I doubt it would matter much anyway.

I’ve started to notice that you can use SQL joins for much more when joining tables then equaling one column to another. This is something I’ve found great usage for and in my view is a nice little thing to be aware of.

Normally – joins are explained something along the line of this (using Inner Join but I guess it doesn’t matter much).
Suppose you have two tables, which needs to be joined:

SELECT *

FROM TableOne AS T1

INNER JOIN TableTwo AS T2 ON T1.ColumnToCompare = T2.ColumnToCompare

This is the way many see joins and utilize them.

Now for a situation I’ve used joins for.

When you map a 1:1 parent-child relationship, and aren’t using nested SQL, then a normal way is to map the child instance with a parent foreign key.
If having a longer parent-child relationship, you could extend the child with a “path” through its parents.
For instance
TableA
Field: ID
Field: ParentID
Field: ParentPath

Very much simplified, then the upmost parent instance could have ID 1 and would have ParentPath 0 and ParentID 0 because it has no parents. The child of ID 1 would thus for example have ID 2 and thus have ParentPath ‘0,1’ and ParentID 1.
Child of ID 2 would thus for example have ID 3 and thus ParentPath ‘0,1,2’ and ParentID 2.

Now to find the parent and the parent’s parent and onwards up, you could utilize a cursor or some other sort of recursive lookup, however trying my best to avoid cursors, I started to utilize the INNER JOIN for this instead.
This will incorporate the usage of PATINDEX, which illustrates the powers of a JOIN statement.
SELECT ID

FROM TableA AS T1

INNER JOIN TableA AS T2

ON PATINDEX('%,' + CAST(T1.ID AS NVARCHAR) + ',%', ',' + T2.ParentPath + ',') > 1

What it does, is that by appending commas to the ID of the T1 of TableA and checking to see if it is located within the ParentPath of T2 of TableA (also appended with commas), you will get the parent, and the parent’s parent and so onwards up the hierarchy.
This means, that in the very simplified situation mentioned, that you’re basically looking for all instances where “,ID,” from TableA is part of the TableA “,ParentPath,”.
It would in the example in question return the ID’s 1 and 2.
You could easily get ID 3 out as well, simply by adding the T1.ID to the end of the ParentPath of T2.

Of course a string search is not the world’s fastest method, but compared to some of the alternatives (cursor for instance), it does provides benefits.

However this also was mainly to show that JOINs are not just a direct column to column matching and it opens up for many tools for using SQL to do stuff.
It’s just experimenting and finding what works for each situation.

SQL Oddity

Sep 112007

I ran into a SQL oddity today in MS SQL 2005 (and likely all prior versions) – or at least I think it’s an oddity.
It revolves around case statements and column aliases, and for some reason you can’t use column alias in a case statement. At least neither in the select nor order by clause.

For example:

SELECT myColumn AS C

FROM myTable

ORDER BY C

This works perfetly well.

Now spice it up with a case (for what ever reason)

SELECT myCoulmn AS C

FROM myTable

ORDER BY

CASE myCondition

WHEN myValue THEN C

END

This for some reason fails because it can’t find column C……

I’ve yet to find the actual reason for why this happens, but that looks odd to me, and it is quite limiting at times.