How to get the desired query statement from the production database and insert the result set into the disk txt file in batch

Step 1: establish a stored procedure

Implement the input parameters and generate the results to the txt file on disk

USE [XXXXX]

GO

/ * Object: StoredProcedure [dbo]. [Proc_sql_to_file] Script Date: 15:47:10 on 2018-5-13 * /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =

-- Author:

-- Create date:

-- Description:

-- =

ALTER PROCEDURE [dbo]. [Proc_sql_to_file] (

@ servername varchar (50),-- server name

@ username varchar (50),-- user name, empty''if NT authentication is used

@ password varchar (50),-- password

@ sql_query varchar (2000),-- SQL query statement, using single quotation marks when there are character conditions

@ filePath_name varchar (1000),-- export path and file name

@ date datetime-the generation date stamp when the file is saved

) 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 @ @ Error0 BEGIN

Print 'made an error while generating the file, please check!' + @ @ Error

END

Step 2: create a new task schedule in the database

Use XXXXX;--- database name

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)

The purchase quantity of Huawei is on the same day.

Select CONVERT (varchar (100,102) + 'incoming code' mobile phone string code, 'quantity' quantity, 'warehouse' warehouse into temp11

Union all

Select

'' + t4.FSERIA no. 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 from T_BD_MATERIALBASE m. FCATEGORYID design. 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'% Mobile% 'and t6.fname like'% Huawei%'

And convert (varchar (10), t1.FDATE _ (21)) = convert (varchar (10), getdate (), 21)

-- quantity in stock

Select CONVERT (varchar (100,102) + 'serial code in library' mobile phone string code, 'quantity' quantity, 'warehouse' warehouse 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) t0roomL 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) an 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 FBASEQTY0) 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'% Huawei%'

Option (maxdop 0)

EXEC XXXXX..Proc_sql_to_file 'DESKTOP-89KSR2K','sa','dingweitao','select * from XXXXX.dbo.temp11','d:\ k3cloud_Instock\ k3cloudkeeper Instock

EXEC XXXXX..Proc_sql_to_file 'DESKTOP-89KSR2K','sa','dingweitao','select * from XXXXX.dbo.temp12','d:\ k3cloud_Inventory\ k3cloudinventor inventor

Go

Drop table temp11,temp12

Go

Eventually, the data will be updated to the file every day.