怎樣從生產(chǎn)數(shù)據(jù)庫中獲得想要的查詢語句，把結(jié)果集批量插入到磁盤txt文件中
第一步：建立存儲過程
成都創(chuàng)新互聯(lián)是一家專業(yè)提供瓊結(jié)企業(yè)網(wǎng)站建設(shè),專注與成都做網(wǎng)站、網(wǎng)站建設(shè)、html5、小程序制作等業(yè)務(wù)。10年已為瓊結(jié)眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站設(shè)計(jì)公司優(yōu)惠進(jìn)行中。
實(shí)現(xiàn)傳入?yún)?shù)，生成結(jié)果到磁盤的txt文件中；
USE [XXXXX]
GO
/****** Object: StoredProcedure [dbo].[Proc_sql_to_file] Script Date: 2018/5/13 15:47:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[Proc_sql_to_file](
@servername varchar(50),--服務(wù)器名
@username varchar(50),--用戶名,如果用NT驗(yàn)證方式,則為空''
@password varchar(50) ,--密碼
@sql_query varchar(2000) , --SQL查詢語句,有字符條件時(shí)用單引號
@filePath_name varchar(1000), --導(dǎo)出路徑和文件名
@date datetime --保存文件時(shí)的生成日期戳
)AS
DECLARE @sql varchar(8000)
SET @sql='bcp "'+@sql_query + '" queryout '+' "'+@filePath_name+'_'+
convert(varchar(10),@date,21)+'.txt " /t "," /w'
+' /S '+@servername
+CASE WHEN ISNULL(@username,'')='' THEN '' ELSE ' /U '+@username END
+' /P '+ISNULL(@password,'')
select @sql
EXEC master..xp_cmdshell @sql
IF @@Error<>0 BEGIN
print '生成文件時(shí)出錯(cuò)了，請查看!'+@@Error
END
第二步：數(shù)據(jù)庫中新建任務(wù)計(jì)劃；
use XXXXX;---數(shù)據(jù)庫名稱
go
if exists(select 1 from sysobjects where name='temp11')
drop table temp11;
if exists(select 1 from sysobjects where name='temp12')
drop table temp12;
go
go
declare @date1 datetime;set @date1 =CONVERT(varchar(10),Getdate(), 21);
--華為采購數(shù)量時(shí)間為當(dāng)天；
select CONVERT(varchar(100), GETDATE(), 102)+'入庫串碼' 手機(jī)串碼,'數(shù)量' 數(shù)量,'倉庫' 倉庫 into temp11
union all
select
' '+t4.FSERIALNO,convert(varchar,1) Fqty,tl.fname FstockName
from T_STK_INSTOCK t1
inner join T_STK_INSTOCKentry t2 on t1.fid=t2.fid
inner join T_STK_INSTOCKENTRY_F t3 on t2.fid=t3.fid
left join T_STK_INSTOCKSERIAL t4 on t3.FENTRYID=t4.FENTRYID
inner join t_bd_material_l t6 on t2.FMATERIALID=t6.FMATERIALID and t6.FLOCALEID=2052
left join T_BD_STOCK_L tl on t2.FSTOCKID=tl.FSTOCKID and tl.FLOCALEID=2052
inner join ( select distinct m.FMATERIALID,m.FCATEGORYID,w.fname from T_BD_MATERIALBASE m
join T_BD_MATERIALCATEGORY_L w on m.FCATEGORYID=w.FCATEGORYID
) u on t6.FMATERIALID=u.FMATERIALID
where u.fname like '%手機(jī)%' and t6.fname like '%華為%'
and convert(varchar(10),t1.FDATE,21)=convert(varchar(10),getdate(),21)
--庫存數(shù)量
select CONVERT(varchar(100), GETDATE(), 102)+'在庫串碼' 手機(jī)串碼,'數(shù)量' 數(shù)量,'倉庫' 倉庫 into temp12
union all
SELECT FNUMBER,convert(varchar,1) Fqty,ckl.FNAME FstockName
FROM (select FNUMBER,FSERIALID,FMATERIALID from V_BD_OVERASERIALVIEW where FSERIALID>1 and FDOCUMENTSTATUS = 'C' AND FFORBIDSTATUS = 'A') t0
INNER JOIN (select FSERIALID from V_BD_OVERASERIALVIEW_L where FSERIALID>1 AND FLocaleId = 2052) t0_L ON (t0.FSERIALID = t0_L.FSERIALID)
INNER JOIN (select FSERIALID From V_BD_OVERASERIALORGVIEW where FSERIALID>1 and FSTOCKSTATUS = 1 ) uu on uu.FSERIALID=t0.FSERIALID
inner join (select FMATERIALID,FNAME FSPMC From t_bd_material_l where FMATERIALID>1 and FLocaleId = 2052) tll on t0.FMATERIALID =tll.FMATERIALID
INNER join (select FCATEGORYID,FMATERIALID From T_BD_MATERIALBASE where FCATEGORYID>1 and FCATEGORYID ='2092334' ) m on t0.FMATERIALID=m.FMATERIALID
left join (SELECT FSERIALID,MIN(FINPUTDATE) FINPUTDATE FROM (select a.FSERIALID FSERIALID,case when b.FINPUTDATE not like '' then b.FINPUTDATE else a.FBILLDATE
end FINPUTDATE from (select FSERIALID,FBILLDATE From T_BD_SERIALBILLTRACE_e where FSERIALID>1 ) a INNER join (select FSERIALID,FINPUTDATE From T_BD_SERIALMASTEROTHER where FSERIALID>1) b on a.FSERIALID=b.FSERIALID ) a GROUP BY FSERIALID) b on t0.fserialid=b.FSERIALID
INNER JOIN (select FSERIALID,FBILLTRACEID,FINVID from T_BD_SERIALBILLTRACE where FBILLTRACEID>1 and FSERIALID>1 ) t5 ON T0.FSERIALID = t5.FSERIALID AND t5.FBILLTRACEID = (SELECT top 1 EE.FBILLTRACEID AS FBILLTRACEID FROM T_BD_SERIALBILLTRACE EE WHERE T0.FSERIALID = EE.FSERIALID and( FINVID is not null and FINVID <> '' ) order by FBILLTRACEID desc)
inner join (select fid,FSTOCKID,FSTOCKSTATUSID from T_STK_INVENTORY where FSTOCKSTATUSID=10000 and FOBJECTTYPEID= 'STK_Inventory' and FKEEPERTYPEID='BD_KeeperOrg'and FOWNERTYPEID='BD_OwnerOrg' and FISEFFECTIVED=1 and FBASEQTY<>0) inv on inv.fid=t5.FINVID
inner join (select FSTOCKID,FNAME from T_BD_STOCK_L where FSTOCKID>1 and FLocaleId = 2052 ) ckl on inv.FSTOCKID=ckl.FSTOCKID
where FSPMC like '%華為%'
option(maxdop 0)
EXEC XXXXX..Proc_sql_to_file 'DESKTOP-89KSR2K','sa','dingweitao','select * from XXXXX.dbo.temp11','d:\k3cloud_Instock\k3cloud_Instock',@date1
EXEC XXXXX..Proc_sql_to_file 'DESKTOP-89KSR2K','sa','dingweitao','select * from XXXXX.dbo.temp12','d:\k3cloud_Inventory\k3cloud_Inventory',@date1
go
drop table temp11,temp12
go
最終每天會更新數(shù)據(jù)到文件中；
網(wǎng)站欄目：怎樣從生產(chǎn)數(shù)據(jù)庫中獲得想要的查詢語句，把結(jié)果集批量插入到磁盤txt文件中
網(wǎng)頁URL：http://m.jiaotiyi.com/article/jeseoo.html