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

Step One: Create a stored procedure

Implement incoming parameters, generate the result into the TXT file of the disk;

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: <Author,,Name>

--Create Date: <create date,,>

--Description: <description, Huawei Manufacturers in order to obtain this system data write this stored procedure, call this process generate TXT file saved to the server, and then FTP to Huawei,>

-- =============================================

ALTER PROCEDURE [dbo]. [Proc_sql_to_file] (

@servername varchar (,--server name)

@username varchar,--user name, or null if NT authentication is used

@password varchar (,--password)

@sql_query varchar,--sql query statement with single quote when character condition

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

@date datetime-The build date stamp when the file is saved

) as

DECLARE @sql varchar (8000)

SET @sql = ' bcp ' [email protected]_query + ' "queryout ' + '" ' [email protected]_name+ ' _ ' +

CONVERT (varchar), @date, +) + '. txt '/t ', '/w '

+ '/S ' [email protected]

+case when ISNULL (@username, ") = ' Then ' ELSE ' u ' [email protected] END

+ '/P ' +isnull (@password, ')

Select @sql

EXEC Master. xp_cmdshell @sql

IF @ @Error <>0 BEGIN

print ' error occurred while generating file, please check! ' [Email protected] @Error

END

The second step: the new task plan 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), Getdate (), 21);

--The amount of time purchased by Huawei is that day;

Select CONVERT (varchar), GETDATE (), 102) + ' Inbound serial code ', ' Quantity ' number, ' warehouse ' warehouse 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 joins 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 joins 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 '% phone% ' and t6.fname like '% Huawei% '

and CONVERT (varchar), T1. fdate,21) =convert (varchar), GETDATE (), 21)

--Inventory quantity

Select CONVERT (varchar (+), GETDATE (), 102) + ' in library string ' 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 F Forbidstatus = ' 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.F Materialid =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 Li Ke ' then b.finputdate else a.fbilldate

End Finputdate from (select Fserialid,fbilldate from T_bd_serialbilltrace_e where fserialid>1) a INNER join (Selec T fserialid,finputdate from T_bd_serialmasterother where fserialid>1) b in A.fserialid=b.fserialid) a GROUP by FSERIAL ID) 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 '% Huawei% '

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

Finally, the data is updated to the file every day;

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