Solved

need Help Inserting from SP into specific columns in a Table

Posted on 2011-02-25
5
204 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
Comment Utility
fyi... The date range will always be 24 Month period.
0
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks... unfortunately, I can't nest two inserts.  ;(
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

728 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

10 Experts available now in Live!

Get 1:1 Help Now