How to Copy query output to the table (with out modifying the script) ?

I have the following table

create table employee (Name varchar(50), avg1 numeric(5),dob datetime,
salary numeric(10,2),location varchar(1),bonus numeric(10,2));

insert into employee values ('AAA',31,'02/19/1980',25000,'A',45000);
insert into employee values ('AA1',44,'08/08/1982',35000,'A',35000);
insert into employee values ('AA3',10,'10/10/1975',25000,'B',50000);
insert into employee values ('AA4',30,'12/10/1999',20000,'B',25000);

Open in new window


How do I insert/copy  the following query result into the table ?

SELECT seq,
   CASE WHEN location IS NULL THEN 'Grand Total' WHEN name IS NULL THEN 'Total' ELSE name END
              name,
          AVG,
          dob,
          salary,
          location,
          bonus
     FROM (SELECT row_number() over(partition by location order by name) seq, t.* FROM employee  t
           UNION ALL
             SELECT NULL,
                          NULL,
                    SUM(AVG) AVG,
                    NULL,
                    SUM(salary) salary,
                    location,
                    SUM(bonus) bonus
               FROM employee 
           GROUP BY ROLLUP(location)) x
 ORDER BY CASE WHEN location IS NULL THEN 1 ELSE 0 END,
          location,
          CASE WHEN x.name IS NULL THEN 1 ELSE 0 END,
          x.name; 

Open in new window

Varshini SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SStoryCommented:
I think you are looking for a SELECT INTO:
http://www.w3schools.com/sql/sql_select_into.asp

So right before (above) your FROM statement
put INTO and the table name the data is to go into.

Obviously the columns from the select (above the INTO part) must be appropriate to fit into the columns of the destination table.
0
Varshini SAuthor Commented:
i try this options but it showing error

select * into temp12  from seq,
   CASE WHEN location IS NULL THEN 'Grand Total' WHEN name IS NULL THEN 'Total' ELSE name END
              name,
          AVG1,
          dob,
          salary,
          location,
          bonus
     FROM (SELECT row_number() over(partition by location order by name) seq, t.* FROM employee  t
           UNION ALL
             SELECT NULL,
                          NULL,
                    SUM(AVG1) AVG,
                    NULL,
                    SUM(salary) salary,
                    location,
                    SUM(bonus) bonus
               FROM employee 
           GROUP BY ROLLUP(location)) x
 ORDER BY CASE WHEN location IS NULL THEN 1 ELSE 0 END,
          location,
          CASE WHEN x.name IS NULL THEN 1 ELSE 0 END,
          x.name; 

Open in new window




Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CASE'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'name'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'x'.
0
SStoryCommented:
I think it would be more like:
SELECT seq,
   CASE WHEN location IS NULL THEN 'Grand Total' WHEN name IS NULL THEN 'Total' ELSE name END
              name,
          AVG,
          dob,
          salary,
          location,
          bonus
     INTO YOUR-DESTINATION-TABLE-NAME
     FROM (SELECT row_number() over(partition by location order by name) seq, t.* FROM employee  t
           UNION ALL
             SELECT NULL,
                          NULL,
                    SUM(AVG) AVG,
                    NULL,
                    SUM(salary) salary,
                    location,
                    SUM(bonus) bonus
               FROM employee 
           GROUP BY ROLLUP(location)) x
 ORDER BY CASE WHEN location IS NULL THEN 1 ELSE 0 END,
          location,
          CASE WHEN x.name IS NULL THEN 1 ELSE 0 END,
          x.name; 

Open in new window


Be sure to replace YOUR-DESTINATION-TABLE-NAME with the correct name.
Again, the columns selected must "fit" the destination table for this to work.  It shouldn't be the same table as the source, either.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.