Solved

need Help Inserting from SP into specific columns in a Table

Posted on 2011-02-25
5
208 Views
Last Modified: 2012-05-11
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
Comment
Question by:epicazo
  • 3
  • 2
5 Comments
 

Author Comment

by:epicazo
ID: 34984010
fyi... The date range will always be 24 Month period.
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34985167
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
 

Author Comment

by:epicazo
ID: 35000420
Server: Msg 8164, Level 16, State 1, Procedure USP_CrossTab, Line 17
An INSERT EXEC statement cannot be nested.
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 250 total points
ID: 35000448
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
 

Author Closing Comment

by:epicazo
ID: 35012154
Thanks... unfortunately, I can't nest two inserts.  ;(
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now