[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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

0
epicazo
Asked:
epicazo
  • 3
  • 2
1 Solution
 
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
 
derekkrommCommented:
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:
Thanks... unfortunately, I can't nest two inserts.  ;(
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now