Error using count in insert query SQL

Question

I've got a query that works fine when I run it on its own, but when I try to put it in a temp table I get the Message:

'An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [ ] are not allowed. Change the alias to a valid name.'

The original query is:

select t.vc, vn, td, bc, COUNT(*) 
FROM Tractors t
join Vehicles tv on t.vc=tv.vc
where Td >= '2013-05-01' and Td <= '2013-05-31' and bc in ('X', 'I')
group by  t.vc, vn, td, bc

The temp table query is:

   IF Object_id(N'tempdb..#TC', N'U') IS NOT NULL DROP TABLE #TC;
   select t.vc, vn, td, bc, COUNT(*) 
   INTO #TC 
   FROM Tractors t
   join Vehicles tv on t.vc=tv.vc
   where Td >= '2013-05-01' and Td <= '2013-05-31' and bc in ('X', 'I')
   group by  t.vc, vn, td, bc

Its probably an obvious fix, but any help would be really appreciated. Thanks

stuartd · Accepted Answer · 2013-12-12 16:57:18Z

3

The error is telling you all columns have to have a name, but your count doesn't, so you have to give it one, e.g.:

select t.vc, vn, td, bc, COUNT(*) AS Tractors

answered Dec 12, 2013 at 16:57

stuartd

73.7k16 gold badges140 silver badges172 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brendan Over a year ago

Yes, to clarify, the fields all need a valid name that the temp table can use as the field name

BWS · Accepted Answer · 2013-12-12 17:04:23Z

1

Based on the error description, each column in the TEMP table needs to have a name. So, try this:

IF Object_id(N'tempdb..#TC', N'U') IS NOT NULL DROP TABLE #TC;
select t.vc, vn, td, bc, COUNT(*) as CNT
INTO #TC 
FROM Tractors t
join Vehicles tv on t.vc=tv.vc
where Td >= '2013-05-01' and Td <= '2013-05-31' and bc in ('X', 'I')
group by  t.vc, vn, td, bc

answered Dec 12, 2013 at 17:04

BWS

3,85620 silver badges26 bronze badges

Collectives™ on Stack Overflow

Error using count in insert query SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related