September 7, 2008 at 10:43 pm
CREATE PROCEDURE CsatDataPull_SP_DP
AS
IF EXISTS (SELECT 1 FROM SYS.SYSOBJECTS WHERE NAME = 'AllRequiredData_newCSAT')
DROP TABLE dbarea.dbo.AllRequiredData_newCSAT
--step 1
select
r.responseid as 'responseid_new',r.respid as 'respid_new',interview_start,interview_end,status,oClientServRep,TLName,[name],companyName,projectType,clientServRep,
clientEmail,mailpd,TLemail,q4Service_1,q4Service_8,q4Service_9,q4Service_2,q4Service_3,q4Service_4,q4Service_6,q4Service_7,
q1_1,q3_1,q3_2,q3_3,q8_1,q5_1,q6_1,q7,urlvar,projectName,projectcompday,projectcompmonth,projectcompyear,oProjectDate,
oClientDevRep,projectvalue_1,team,GMName,GMemail,teamemail,csatsent,notsendinvite,surveyresult,TeamLeader,topclient,
cWeek,allfive,q4Service_5,q4Service_10,q4Service_11HAP,q4Service_12HAP,q4Service_13HAP,q4Service_14HAP,q4Service_15HAP,
q4Service_16HAP,q4Service_17HAP,pdlowcount,RankPd_1,projecttypeattributes,postivecount,Ptagiven,FIArespid,positivecount_1,
avgscore_1,projectgroup
into dbarea.dbo.AllRequiredData_newCSAT
from CF9SQLa.survey_p19268007.dbo.response0 r ,
CF9SQLa.survey_p19268007.dbo.response_control rc,
CF9SQLa.survey_p19268007.dbo.response1 r1
where r.respid=rc.respid
AND R1.RESPID=R.RESPID
and status='complete'
--step 2
begin tran
alter table dbarea.dbo.AllRequiredData_newCSAT
add responseid int identity,
respid int,
sp_score smallint,
dp_score smallint
commit
---step 3
begin tran
update dbarea.dbo.AllRequiredData_newCSAT
set respid=responseid
from dbarea.dbo.AllRequiredData_newCSAT
commit
------------------------------------------------------------------------
Above is my sp script , i m sorry i cant give you table schema or data
But
Problem is that :
step 3 is get executed BEFORE step 2 ...which results in error ...
Can this happen becusee of large amount amount data is inserted into step 2 ?
Please Help me
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 8, 2008 at 2:33 am
Bhuvnesh,
SQL Server detects that the columns are missing before running the code, but doesn't see that the new columns are added to the table in step 2.
However, you don't need step 2:
[font="Courier New"]SELECT IDENTITY (INT, 1, 1) AS responseid,
CAST(NULL AS INT) AS respid,
CAST(NULL AS smallint) AS sp_score,
CAST(NULL AS smallint) AS dp_score,
r.responseid AS 'responseid_new',r.respid AS 'respid_new',interview_start,interview_end,status,oClientServRep,TLName,[name],companyName,projectType,clientServRep,
clientEmail,mailpd,TLemail,q4Service_1,q4Service_8,q4Service_9,q4Service_2,q4Service_3,q4Service_4,q4Service_6,q4Service_7,
q1_1,q3_1,q3_2,q3_3,q8_1,q5_1,q6_1,q7,urlvar,projectName,projectcompday,projectcompmonth,projectcompyear,oProjectDate,
oClientDevRep,projectvalue_1,team,GMName,GMemail,teamemail,csatsent,notsendinvite,surveyresult,TeamLeader,topclient,
cWeek,allfive,q4Service_5,q4Service_10,q4Service_11HAP,q4Service_12HAP,q4Service_13HAP,q4Service_14HAP,q4Service_15HAP,
q4Service_16HAP,q4Service_17HAP,pdlowcount,RankPd_1,projecttypeattributes,postivecount,Ptagiven,FIArespid,positivecount_1,
avgscore_1,projectgroup
INTO dbarea.dbo.AllRequiredData_newCSAT
FROM CF9SQLa.survey_p19268007.dbo.response0 r ,
CF9SQLa.survey_p19268007.dbo.response_control rc,
CF9SQLa.survey_p19268007.dbo.response1 r1
WHERE r.respid=rc.respid
AND R1.RESPID=R.RESPID
AND status='complete'
[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply