need Help Inserting from SP into specific columns in a Table

I have a table RPT_ADMISSION24 and I would like to insert data from my SP -- see attached code.    

CREATE TABLE [dbo].[Rpt_Admission24] (
      [YearMonthofReport] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [PDSPCL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [DoctorName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Month1] [numeric](18, 0) NULL ,
      [Month2] [numeric](18, 0) NULL ,
      [Month3] [numeric](18, 0) NULL ,
      [Month4] [numeric](18, 0) NULL ,
      [Month5] [numeric](18, 0) NULL ,
      [Month6] [numeric](18, 0) NULL ,
      [Month7] [numeric](18, 0) NULL ,
      [Month8] [numeric](18, 0) NULL ,
      [Month9] [numeric](18, 0) NULL ,
      [Month10] [numeric](18, 0) NULL ,
      [Month11] [numeric](18, 0) NULL ,
      [Month12] [numeric](18, 0) NULL ,
      [Year1Total] AS ([Month1] + [Month2] + [Month3] + [Month4] + [Month5] + [Month6] + [Month7] + [Month8] + [Month9] + [Month10] + [Month11] + [Month12]) ,
      [Month13] [numeric](18, 0) NULL ,
      [Month14] [numeric](18, 0) NULL ,
      [Month15] [numeric](18, 0) NULL ,
      [Month16] [numeric](18, 0) NULL ,
      [Month17] [numeric](18, 0) NULL ,
      [Month18] [numeric](18, 0) NULL ,
      [Month19] [numeric](18, 0) NULL ,
      [Month20] [numeric](18, 0) NULL ,
      [Month21] [numeric](18, 0) NULL ,
      [Month22] [numeric](18, 0) NULL ,
      [Month23] [numeric](18, 0) NULL ,
      [Month24] [numeric](18, 0) NULL ,
      [Year2Total] AS ([Month13] + [Month14] + [Month15] + [Month16] + [Month17] + [Month18] + [Month19] + [Month20] + [Month21] + [Month22] + [Month23] + [Month24])
)
exec USP_CrossTab '
SELECT HPP.ccGrpName, HPP.ccYRMM, MDP.PDSPCL, MDP.PDNAME AS DoctorName, 1 as cntr
FROM  dbo.VW_HPPATMFL HPP
INNER JOIN dbo.VW_MasterDoctorPt MDP ON HPP.PPGRPno = MDP.PJGRP# AND HPP.PPPATno = MDP.PJPAT#
WHERE (HPP.PPTYPE = ''IP'') 
AND (HPP.PPMTCD <> ''D'') 
AND (mdp.PJAFTP = ''2'') 
AND (HPP.ccYRMM BETWEEN ''2009-01'' AND ''2009-03'')
AND (MDP.PDSPCL <> ''nur'') 
AND (HPP.ccGrpName = ''ELA'')
',
'ccYRMM',
'count(cntr)[]',
'PDSPCL,DoctorName' 


DATA OUTPUT...
PDSPCL DoctorName                2009-01     2009-02     2009-03     
------ ------------------------- ----------- ----------- ----------- 
PED    ABDEL-MALEK, SHAHIRA      15          12          18
OBG    AGUILAR, ALEJANDRO        33          21          39
IM     ALVARADO, PATRICIA        0           3           0
IM     ARROYO, JOEL              3           3           0
PUL    BANSAL, JAGAN             21          33          45
GEN    BANSAL, MANEESH A.        21          18          30
GS     BARRAGAN, ALFONSO         3           3           3
GEN    BASCOY, LOUIS             24          18          18
OBG    BAYLON, PAUL              6           18          3
GEN    BIRMAN, HECTOR            48          42          27
IM     CARABELLO, VICTOR         18          9           36
IM     CASTILLO, ROMEO           18          24          24
OBG    CHOI, MARK                0           0           6
IM     CHOWDHURY, PRADIP         0           6           24
OBG    CHUNG, NAMKI              6           6           18
OBG    DANESHGAR, KEVIN          81          30          42
GEN    DELGADO, JOSE             0           0           3
IM     DHAWAN, VIJAY             150         114         84
IM     ESPENAN, PIERRE           15          21          9
OBG    FARHAT, HORMOZ            18          12          39
CD     FARINHA, JOSE             3           6           0
GEN    FLEISCHMAN, HORACIO       3           6           3
OTO    FORD, GARY                3           0           0
GS     FOX, ARTHUR               24          21          15
GEN    GONZALEZ, BERTHA          0           0           3
IM     HARO, CARLOS              51          27          24
OBG    HASHEMI, ZIA              3           0           3
GEN    HO, HAREL                 15          6           9
PED    JEYARANJAN, THAMBIM       78          156         114
GEN    KAHEN, NISSAN             3           0           0
NEP    KANAKARAJ, KAVITHA        0           3           3
OBG    KHALATIAN, ROUBEN         33          27          21
CD     KING, JANINE M            36          39          66
OBG    LAM, JACINTO              21          24          15
GEN    LIN, JOHN                 24          24          33
GS     MARTINEZ, HUMBERTO        0           0           3
IM     MEZA, CARLOS              0           0           3
OBG    NASSIR, ALBERT            0           6           3
GEN    NAVARRETE, JORGE          3           3           6
GS     NAVAS, RICARDO            0           0           3
PED    NGUYEN, DAVID DO          90          45          42
PED    NGUYEN, TAO T.            54          48          90
999    NON STAFF                 3           0           6
GEN    NWAIGWE, MANASSEH         3           3           3
GS     ONG, ANTONIO              0           0           3
CD     PALMER H, SCHUBERT        0           0           3
NEP    PASHAYAN, SHAHE           0           3           3
NEP    PENG, LUON W.             9           9           12
IM     REDDY, JANGA A            18          18          18
OBG    REYES, BENIGNO            27          27          36
OBG    ROMERO, JESUSA            36          18          27
GS     SCHIBLER, DANIEL          3           0           0
GEN    SHAH, KIRITKUMAR          36          33          18
GEN    SHUE, RANDALL             3           3           0
GS     SMITH, MONT               0           6           9
PUL    SUAREZ, DANIEL            3           3           0
NEP    TOSCANO, EDGAR            18          24          3
IM     TRAN, TU                  0           0           3
GEN    VELEZ, CESAR              27          33          54
PED    WIJESEKERA, STANLEY       72          24          102
URO    WISNIA, LAZARO            0           3           3
PED    YAZDI, REZA               3           3           0
IM     ZAPATA, MARTIN            54          45          60
IM     ZEVALLOS, EDWIN W         6           12          3

Open in new window

epicazoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
derekkrommConnect With a Mentor Commented:
Ahh, got ya.

Check out this article: http://www.sommarskog.se/share_data.html

It does an excellent job of outlining exactly what your options are and why.
0
 
epicazoAuthor Commented:
fyi... The date range will always be 24 Month period.
0
 
derekkrommCommented:
you should just be able to do "insert into <tbl> exec <proc>"

see this example:
create proc spTest as

select 1,2,3,4 union select 5,6,7,8
go

create table tTest (x varchar(10), a int, b int, c int, d int)
go

insert into tTest(a,b,c,d)
exec spTest
go

select * from tTest
go

Open in new window

0
 
epicazoAuthor Commented:
Server: Msg 8164, Level 16, State 1, Procedure USP_CrossTab, Line 17
An INSERT EXEC statement cannot be nested.
0
 
epicazoAuthor Commented:
Thanks... unfortunately, I can't nest two inserts.  ;(
0
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.

All Courses

From novice to tech pro — start learning today.