请帮我将数据从sql临时表传输到文件 [英] Please help me transfer the data from sql temporary table to a file
问题描述
我在这里粘贴存储过程代码。此存储过程由应用程序(在C#中)进行处理,以在.CSV中生成报告。存储过程适用于小数据,但如果数据很大(99%的时间),则sp无法发送整个数据,因此我必须以块的形式分解数据并将其提供给.CSV。我尝试了很多东西,比如将数据分成块,然后尝试将其发送到.CSV,但它没有帮助。只有第一批数据才能在.CSV中找到。请帮助我,以便我可以发送数据而不是批量数据。
注意:请专注于评论在下面的SP中:这个选择查询将整个数据发送到报告,我想我们必须在这里更改代码 - 上半部分的代码生成报告并对其进行格式化。
从slno记录订单中选择记录
请在最早的时候帮助我
存储过程:
Hi,
I am pasting a stored procedure code here. this stored procedure is caleed by an application( in C#) to generates a report in .CSV. The stored procedure works fine for small data but if the data is huge ( 99% of the times ) , the sp is not able to send the entire data, hence I have to break the data in chunks and give it to the .CSV. i tried many thing , like dividing the data in chunks and then try sending it to the .CSV, but it did not help. ONLY THE FIRST CHUNK OF DATA WAS FOUND IN THE .CSV. Please help me so that i can send the data in chunks rather than bulk.
NOTE : Please concentrate on the comment in the below SP: This select query sends the entire data to the report, i guess we have to change the code here-- The code in the upper half generates the report and format it.
select record from records order by slno
PLEASE HELP ME AT THE EARLIEST
STORED PROCEDURE :
ALTER PROCEDURE [dbo].[USCfp_GenerateReport](
@ReportDt datetime,
@RetCode int output
)
AS
SET NOCOUNT ON
DECLARE @IsRecreate BIT,
@RptLastRunDt DATETIME,
@RptStartDt DATETIME,
@RptEndDt DATETIME,
@RowCount int,
@startAR money,
@endAR money,
@startDR money,
@endDR money,
@now datetime,
@LastTransDate datetime,
@MtrNo int,
@PieceCnt bigint
create table #detailedrecord
(
MpdMtrNo int,
MpdEndDate varchar(10),
MpdCategoryCD varchar(20),
WW6_ITEM_CD varchar(10),
MpdTolPieceCnt bigint,
MpdTolValue money
)
create table #RateCategory
(Bucket_Name varchar(20),
Rate_Category varchar(20))
Create table #Records
(
slno int identity(1,1),
RecordType varchar(10),
Record varchar(1000),
DiffAR money ,
TolCount varchar(20)
)
create table #startSRDetails
(
MpsMtrNo int,
MpsStartAscReg money,
MpsStartDescReg money,
WW6_ITEM_CD varchar(10)
)
create table #endSRDetails
(
MpsMtrNo int,
MpsEndAscReg money,
MpsEndDescReg money,
MaxMpsAddDate datetime
)
set @RetCode=0
BEGIN TRANSACTION
IF @ReportDt IS NOT NULL
SET @IsRecreate = 1
ELSE
SET @IsRecreate = 0
/* IF THE REPORT IS BEING RE-CREATED, THEN IT SHOULD BE ORIGINALLY CREATED ON @ReportDt(File Creation Date). IF IT IS NOT
* ABORT EXTRACTION
*/
IF @IsRecreate = 1
BEGIN
/*IF EXTRACTED PREVIOUSLY ON THE DATE @RPTDT, CONTINUE WITH RE-GENERATION ELSE ABORT RECREATION
SET THE @RETCODE TO 1. MESSAGE HANDLED APPROPRIATELY IN THE APPLICATION*/
IF EXISTS (SELECT WW50_JOB_RUN_DT
FROM WW050T
WHERE WW50_USER_ID = 'PSRExtract'
AND DATEDIFF(D,@ReportDt,WW50_JOB_RUN_DT) = 0)
BEGIN
SELECT @ReportDt = WW50_JOB_RUN_DT
FROM WW050T
WHERE WW50_USER_ID = 'PSRExtract'
AND DATEDIFF(D,@ReportDt,WW50_JOB_RUN_DT) = 0
SELECT @RptLastRunDt = MAX(WW50_JOB_RUN_DT)
FROM WW050T
WHERE WW50_USER_ID = 'PSRExtract'
AND WW50_JOB_RUN_DT < @ReportDt
END
ELSE
BEGIN
SET @RetCode = 1
GOTO Proc_Err
END
END
ELSE
BEGIN
--set report date as today's date
SET @ReportDt = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
SELECT @RptLastRunDt = MAX(WW50_JOB_RUN_DT)
FROM WW050T WHERE WW50_USER_ID = 'PSRExtract'
AND WW50_JOB_RUN_DT <= @ReportDt
END
/*IF THE @RPTLASTRUNDT AND @RPTDT ARE IDENTICAL, IT IMPLIES THAT THE REPORT HAS ALREADY
BEEN EXTRACTED FOR THE DAY*/
IF @RptLastRunDt = @ReportDt
BEGIN
SET @RetCode = 2
GOTO Proc_Err
END
--Compute USPS report start date [first day of the month of last successful extraction prior to @RptDt]
SET @RptStartDt = CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@RptLastRunDt))+RIGHT(REPLICATE('0',2) +
CONVERT(VARCHAR,MONTH(@RptLastRunDt)),2)+'01',112)
--Compute USPS report end date [last day of the month prior to @RptDt]
SET @RptEndDt = CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@ReportDt))+RIGHT(REPLICATE('0',2) +
CONVERT(VARCHAR,MONTH(@ReportDt)), 2)+'01',112)
IF @@ERROR <> 0 GOTO Proc_Err
--Detail Records
insert into #detailedrecord
SELECT MpdMtrNo,
RIGHT(REPLICATE('0',2) + CONVERT(VARCHAR,MONTH(MpdEndDate)),2) + CONVERT(VARCHAR,YEAR(MpdEndDate))
,MpdCategoryCD,LEFT(WW6_ITEM_CD,2),Convert(bigint,MpdTolPieceCnt),MpdTolValue
FROM MtrPeriodDetails mpd
INNER JOIN WW006T ww6
ON mpd.MpdMtrNo = ww6.WW6_MTR_NO
INNER JOIN MtrModelConfig mmc
ON ww6.WW6_ITEM_CD=mmc.MMC_ITEM_CD and mmc.MMC_DEVICE_TYPE in (20,21,28)
and (MpdAddDate >= @RptStartDt) AND (MpdAddDate < @ReportDt)and --To include all the records whose Add date falls between Report Start date and current date
NOT(MpdAddDate < @RptLastRunDt AND MpdEndDate < @RptStartDt)
AND (MpdEndDate < @RptEndDt)
order by
MpdMtrNo,
LEFT(WW6_ITEM_CD,2),
RIGHT(REPLICATE('0',2) + CONVERT(VARCHAR,MONTH(MpdEndDate)),2) + CONVERT(VARCHAR,YEAR(MpdEndDate)),
MpdCategoryCD
insert into #RateCategory
select distinct Bucket_Name,Rate_Category from ratecategory, MtrPeriodDetails where MpdCategoryCD=Bucket_Name
insert into #Records
select 'DT','DT'+','+ CONVERT(VARCHAR,@ReportDt,112)+',' +MpdEndDate+ ','+'02'+','+t1.WW6_ITEM_CD +','
+Convert(varchar,RIGHT(REPLICATE('0',10) + CONVERT(VARCHAR,t1.MpdMtrNo),10))+','+Rate_Category +','
+RIGHT(REPLICATE('0',7) + Convert(varchar,sum(t1.MpdTolPieceCnt)),7)+','+
RIGHT(REPLICATE('0',13) + CONVERT(VARCHAR,(CONVERT(BIGINT,(sum(t1.MpdTolValue* 1000))))),13),sum(t1.MpdTolValue),RIGHT(REPLICATE('0',7) + Convert(varchar,sum(t1.MpdTolPieceCnt)),7)
from #detailedrecord t1
inner join
(select Bucket_Name,Rate_Category from #RateCategory )R
on t1.MpdCategoryCD=R.Bucket_Name
GROUP BY
t1.MpdMtrNo,
t1.WW6_ITEM_CD,
t1.MpdEndDate,
R.Rate_Category
ORDER BY
t1.MpdMtrNo,
t1.WW6_ITEM_CD ,
t1.MpdEndDate,
R.Rate_Category
insert into #startSRDetails
SELECT t1.MpsMtrNo as MtrNo, t1.MpsStartAscReg as StartAR, t1.MpsStartDescReg as StartDR,TempMpsMin.WW6_ITEM_CD as ItemCd
FROM MtrPeriodSummary t1
INNER JOIN (
SELECT MpsMtrNo, Min(MpsEndDate) As EndDate ,WW6.WW6_ITEM_CD
FROM MtrPeriodSummary mps
INNER JOIN WW006T ww6
ON mps.MpsMtrNo = ww6.WW6_MTR_NO
INNER JOIN MtrModelConfig mmc
ON ww6.WW6_ITEM_CD=mmc.MMC_ITEM_CD and mmc.MMC_DEVICE_TYPE in (20,21,28)
and (mps.MpsAddDate >= @RptStartDt) AND (mps.MpsAddDate < @ReportDt)and --To include all the records whose Add date falls between Report Start date and current date
NOT(mps.MpsAddDate < @RptLastRunDt AND mps.MpsAddDate < @RptStartDt)
AND (mps.MpsEndDate < @RptEndDt)
group by mps.mpsmtrno,WW6.WW6_ITEM_CD
) TempMpsMin
ON t1.MpsMtrNo = TempMpsMin.MpsMtrNo and t1.MpsEndDate=TempMpsMin.EndDate
insert into #endSRDetails
SELECT t1.MpsMtrNo as MtrNo, t1.MpsEndAscReg as EndAR, t1.MpsEndDescReg as EndDR, MaxAddDate as EndDate
FROM MtrPeriodSummary t1
INNER JOIN (
SELECT MpsMtrNo, Max(MpsAddDate) as MaxAddDate,Max(MpsEndDate) As MaxEndDate
FROM MtrPeriodSummary mps
INNER JOIN WW006T ww6
ON mps.MpsMtrNo = ww6.WW6_MTR_NO
INNER JOIN MtrModelConfig mmc
ON ww6.WW6_ITEM_CD=mmc.MMC_ITEM_CD and mmc.MMC_DEVICE_TYPE in (20,21,28)
and (mps.MpsAddDate >= @RptStartDt) AND (mps.MpsAddDate < @ReportDt)and --To include all the records whose Add date falls between Report Start date and current date
NOT(mps.MpsAddDate < @RptLastRunDt AND mps.MpsAddDate < @RptStartDt)
AND (mps.MpsEndDate < @RptEndDt)
group by mps.mpsmtrno
) TempMpsMax
on t1.MpsMtrNo = TempMpsMax.MpsMtrNo and t1.MpsEndDate=TempMpsMax.MaxEndDate
insert into #Records
select 'SR','SR'+','+'02'+','+LEFT(WW6_ITEM_CD,2)+','+RIGHT(REPLICATE('0',10) + CONVERT(VARCHAR,S.MpsMtrNo),10)+','+CONVERT(VARCHAR,getdate(),112)+',' +
RIGHT(REPLICATE('0',18) + CONVERT(VARCHAR,(CONVERT(BIGINT,(S.MpsStartAscReg * 1000)))),18) +','+
RIGHT(REPLICATE('0',18) + CONVERT(VARCHAR,(CONVERT(BIGINT,(E.MpsEndAscReg * 1000)))),18)+','+
RIGHT(REPLICATE('0',18) + CONVERT(VARCHAR,(CONVERT(BIGINT,(S.MpsStartDescReg * 1000)))),18)+','+
RIGHT(REPLICATE('0',18) + CONVERT(VARCHAR,(CONVERT(BIGINT,(E.MpsEndDescReg * 1000)))),18) +','+
CONVERT(VARCHAR,E.MaxMpsAddDate,112),
0,0
from #startSRDetails S
INNER JOIN #endSRDetails E
on S.MpsMtrNo=E.MpsMtrNo
order by S.MpsMtrNo
IF @@ERROR <> 0
Begin
GOTO Proc_Err
End
--Trailer Record
insert into #Records
select 'TR','TR'+','+'02'+','+CONVERT(VARCHAR,@ReportDt,112) + ','+
RIGHT(REPLICATE('0',11)+Convert(varchar(20),(select count(*) from #Records where RecordType like 'DT%')),11) + ','+
RIGHT(REPLICATE('0',3)+Convert(varchar(20),(select count(distinct(WW6_ITEM_CD)) from #detailedrecord)),3)+','+
RIGHT(REPLICATE('0',10)+Convert(varchar(20),Convert(bigint,(select sum(Convert(bigint,TolCount)) from #Records where RecordType='DT'))),10)+','+
RIGHT(REPLICATE('0',18)+Convert(varchar(20),Convert(bigint,(select (sum(DiffAR)*1000) from #Records where RecordType='DT'))),18),0,0
IF @@ERROR <> 0
Begin
GOTO Proc_Err
End
-- This select query sends the entire data to the report, i guess we have to change the code here--
select record from records order by slno
--If the report is being created, update WW050T with run date. Not required,if report is being regenerated.
IF @IsRecreate <> 1
INSERT INTO WW050T
(WW50_JOB_RUN_DT,
WW50_USER_ID,
WW50_SELECT_START_DT,
WW50_SELECT_END_DT)
VALUES
(@ReportDt,
'PSRExtract',
@RptStartDt,
@RptEndDt)
--Main Logic: End
COMMIT TRANSACTION
GOTO Proc_Exit
Proc_Err:
ROLLBACK TRANSACTION
RETURN @RetCode
Proc_Exit:
SET NOCOUNT OFF
RETURN @RetCode
推荐答案
这篇关于请帮我将数据从sql临时表传输到文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!