Where to put insert for table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Where to put insert for table

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-06 : 16:16:26

I have this long query that works but now I want to put it into a new table. When put into Test, in front of the from I'm getting these errors:

Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'into'.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'GROUP'.
Msg 102, Level 15, State 1, Line 81
Incorrect syntax near ')'.

Their location is in bold below. What am I missing?


Select b.doc, sum(b.pendinfo) as pendinfo, sum(b.pendover) as pendover,sum(pendndds) as pendndds,
sum(pendndds300) as pendndds300,
sum(pend250) as pend250, sum(pend300) as pend300,
sum(pend350) as pend350, sum(pend399) as pend399,
sum(pendinfo) + sum(pendndds) as Totpend

from
(
SELECT t.doc, ISNULL(COUNT(distinct t.clm), 0)AS pendinfo, 
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 300 then  t.clm else null end), 0)AS pendover,
CAST(NULL as int) AS pendndds,
CAST(NULL as int) AS pendndds300,
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 249 and Datediff(Day, t.app_rcpdt, Getdate()) < 301 then  t.cos else null end), 0) as pend250,
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 300 and Datediff(Day, t.app_rcpdt, Getdate()) < 351 then  t.clm else null end), 0) as pend300,
Isnull(COUNT(distinct case when Datediff(Day, t.app_rcpdt, Getdate()) > 350 and Datediff(Day, t.app_rcpdt, Getdate()) < 401 then  t.clm else null end), 0) as pend350,
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 399 then  t.clm else null end), 0)AS pend399
into Test   
FROM   t2dibpend t  
Where DIB_MVT_SEQ_NUM is NULL and t.cos not in (select cos from dbo.people where completedt is null)
     GROUP  BY t.doc
union all
SELECT t.doc, Isnull(COUNT(distinct t.clm), 0) AS pendinfo, 
Isnull(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 300 then  t.clm else null end), 0) AS pendover,
NULL,
NULL,
Isnull(COUNT(distinct case when Datediff(Day, app_rcpdt, Getdate()) > 249 and Datediff(Day, app_rcpdt, Getdate()) < 301 then  t.clm else null end), 0) as pend250,
Isnull(COUNT(distinct case when Datediff(Day, app_rcpdt, Getdate()) > 300 and Datediff(Day, app_rcpdt, Getdate()) < 351 then  t.clm else null end), 0) as pend300,
Isnull(COUNT(distinct case when Datediff(Day, app_rcpdt, Getdate()) > 350 and Datediff(Day, app_rcpdt, Getdate()) < 401 then  t.clm else null end), 0) as pend350,
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 399 then  t.clm else null end), 0)AS pend399
into test
from
(select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm 
 From t2DibPend t 
group by clm)b
left join 
t2dibpend t on b.dib_mvt_seq_num=t.dib_mvt_seq_num and b.clm=t.clm
 where(DIB_MVT_TYP='r')  and not (LOREC4 like 'R%' or LOREC4 like 'S%' or LOREC4 like 'V%' or
LOREC4 like 'P%') and t.cos not in (select cos from dbo.people where completedt is null)
group by t.doc

 union all
SELECT t.doc, Isnull(COUNT(distinct t.clm), 0) AS pendinfo, 
Isnull(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 300 then  t.clm else null end), 0) AS pendover,
NULL,
NULL,
Isnull(COUNT(distinct case when Datediff(Day, app_rcpdt, Getdate()) > 249 and Datediff(Day, app_rcpdt, Getdate()) < 301 then  t.clm else null end), 0) as pend250,
Isnull(COUNT(distinct case when Datediff(Day, app_rcpdt, Getdate()) > 300 and Datediff(Day, app_rcpdt, Getdate()) < 351 then  t.clm else null end), 0) as pend300,
Isnull(COUNT(distinct case when Datediff(Day, app_rcpdt, Getdate()) > 350 and Datediff(Day, app_rcpdt, Getdate()) < 401 then  t.clm else null end), 0) as pend350,
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 399 then  t.clm else null end), 0)AS pend399
into test
from
(select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm 
 From t2DibPend t 
group by clm)c
left join 
t2dibpend t on c.dib_mvt_seq_num=t.dib_mvt_seq_num and c.clm=t.clm
 where (DIB_MVT_TYP='T') and t.cos not in (select cos from dbo.people where completedt is null)
group by t.doc
--WHERE  (mvt_typ = 'R' or mvt_typ='T' ) and not (mvt_loc LIKE '[RSV]%')
--       group by t.fo
  
union all

 SELECT t.doc,NULL,NULL, 
Isnull(COUNT(distinct t.clm), 0) AS pendndds,
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 300 then  t.clm else null end), 0)AS penddds300,
Isnull(COUNT(distinct case when Datediff(Day, app_rcpdt, Getdate()) > 249 and Datediff(Day, app_rcpdt, Getdate()) < 301 then  t.clm else null end), 0) as pend250,
Isnull(COUNT(distinct case when Datediff(Day, app_rcpdt, Getdate()) > 300 and Datediff(Day, app_rcpdt, Getdate()) < 351 then  t.clm else null end), 0) as pend300,
Isnull(COUNT(distinct case when Datediff(Day, app_rcpdt, Getdate()) > 350 and Datediff(Day, app_rcpdt, Getdate()) < 401 then  t.clm else null end), 0) as pend350,
ISNULL(COUNT(distinct case when Datediff(DAY, t.app_rcpdt, Getdate()) > 399 then  t.clm else null end), 0)AS pend399
into test
from
(select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm
 From t2DibPend t 
group by clm)d
left join 
t2dibpend t on d.dib_mvt_seq_num=t.dib_mvt_seq_num and d.clm=t.clm
 where(DIB_MVT_TYP='r') and (LOREC4 like '[RSV]%')
 and t.cos not in (select cos from dbo.people where completedt is null)
group by t.doc)as b
Group By b.doc
order by b.doc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-06 : 16:19:12

You can't put the INTO in that location. It can't be in a derived table.

It needs to be here:

Select b.doc, sum(b.pendinfo) as pendinfo, sum(b.pendover) as pendover,sum(pendndds) as pendndds,
sum(pendndds300) as pendndds300,
sum(pend250) as pend250, sum(pend300) as pend300,
sum(pend350) as pend350, sum(pend399) as pend399,
sum(pendinfo) + sum(pendndds) as Totpend
into Test
from
...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 01:33:17

there are couple of other issues too. select into creates a table and puts data to it. you're doing it multiple times so it will end up creating table again and again and trhrow error. why not create table before and use insert....select in all places where you want to populate.

also remove the , at the end of column list just before into clause

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-07 : 08:01:09

Thanks to you both!!! Could you explain derived tables to me a bit more please.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-07 : 08:37:42

Derived table is sort of an inline view and you're already using it, maybe you're just not aware of the name:

select 
  ...
from
  (select max(dib_mvt_seq_num) as dib_mvt_seq_num, clm 
   From t2DibPend t 
  group by clm) b
left join ...

The subselect in this script is referred to as a derived table.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-07 : 13:17:21

I certainly didn't realize it. Thanks so much for the visual.

Thanks visakh16 and tkizer for your help again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 13:22:01

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources