CODEWITHSUNDEEP
sqlsql-serverinsertcount
Käse
Käse

Reputation: 615

Error using count in insert query SQL

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

Upvotes: 0

Views: 105

Answers (2)

BWS
BWS

Reputation: 3846

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

Upvotes: 1

stuartd
stuartd

Reputation: 73253

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

Upvotes: 3

Related Questions