CODEWITHSUNDEEP
sql-server-2008
edgarmtze
edgarmtze

Reputation: 25038

Error inserting into temporary table

I have a query but I get an error:

Query:

INSERT #tableN (ID,NAME,Value) 
  SELECT 1 , 'subject', SUM(subject), 2, 'age', SUM(age), 
         3, 'sex', SUM(sex), 4, 'test_time', SUM(test_time) 
  FROM taleParker

I get the following error:

Msg 203, Level 16, State 2, Line 61
The name 'INSERT #tableN (ID,NAME,Value) SELECT 1 , 'subject', SUM(subject),2 , 'age', SUM(age),3 , 'sex', SUM(sex),4 , 'test_time', SUM(test_time),' is not a valid identifier.

Upvotes: 0

Views: 692

Answers (2)

marc_s
marc_s

Reputation: 754398

Your temporary table has three columns - yet your SELECT to insert has way more....

You need to break this up into several INSERTs:

INSERT #tableN (ID,NAME,Value) 
  SELECT 1 , 'subject', SUM(subject)
  FROM taleParker

INSERT #tableN (ID,NAME,Value) 
  SELECT 2, 'age', SUM(age)
  FROM taleParker

INSERT #tableN (ID,NAME,Value) 
  SELECT 3, 'sex', SUM(sex)
  FROM taleParker

INSERT #tableN (ID,NAME,Value) 
  SELECT 4, 'test_time', SUM(test_time) 
  FROM taleParker

Update: in SQL Server 2008, you could have:

INSERT #tableN (ID,NAME,Value) 
   VALUES(1, 'subject', 1), (2, 'age', 42), (3, 'sex', 'M') .....

but then you have to use fixed values - not SELECT expressions

Update #2: if you want to use a SUM(...) value, you'd have to get those first into variables:

DECLARE @SumSubj INT, @SumAge INT, @SumSex INT, @SumTest INT

SELECT @SumSubj = SUM(subject), @SumAge = SUM(age),
       @SumSex = SUM(sex), @SumTest = SUM(test_time)
FROM taleParker

INSERT #tableN (ID,NAME,Value) 
   VALUES(1, 'subject', @SumSubj), (2, 'age', @SumAge), (3, 'sex', @SumSex) .....

Upvotes: 2

Ken White
Ken White

Reputation: 125671

INSERT #tableN (ID,NAME,Value) 
  SELECT 1, 'subject', SUM(subject) FROM taleParker  UNION ALL
  SELECT 2, 'age', SUM(age) FROM taleParker  UNION ALL
  SELECT 3, 'sex', SUM(sex) FROM taleParker UNION ALL 
  SELECT 4, 'test_time', SUM(test_time) FROM taleParker

Upvotes: 2

Related Questions