USE [TMPDB]
GO
/****** Object: StoredProcedure [dbo].[SalaryFileUpload] Script Date: 2018/3/29 上午 09:40:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: 資料上傳至DB
-- Exec SalaryFileUpload ''
-- EXec SalaryFileUpload 'D:\SalaryFile\UploadFile20150821.csv'
-- add Education Career Income
-- =============================================
CREATE PROCEDURE [dbo].[SalaryFileUpload]
(
@filename AS VARCHAR(100) --File Path
)
AS
BEGIN
DECLARE @bulk_cmd varchar(MAX);
DECLARE @SystemDate varchar(8);
SET @SystemDate = (Select Convert(char(8),getdate(),112) )
if @filename = ''
begin
SET @filename='D:\SalaryFile\UploadFile' + @SystemDate + '.csv'
end
PRINT '**** 執行開始 = SalaryFileUpload' + convert(char(23),getdate(),21)
PRINT '**** 準備要上傳的檔名= ' + @filename
PRINT '**** 產生暫存的Table = SalaryCIF_Tmp'
CREATE TABLE [dbo].[SalaryCIF_Tmp](
[CustID] [varchar](20) NULL,
[CustName] [varchar](50) NULL,
[EnCustName] [varchar](50) NULL,
[Birth] [char](8) NULL,
[CustTel] [varchar](20) NULL,
[CompTel] [varchar](20) NULL,
[Mobile] [varchar](20) NULL,
[AddressArea_1] [varchar](20) NULL,
[Address_1] [varchar](80) NULL,
[AddressArea_2] [varchar](20) NULL,
[Address_2] [varchar](80) NULL,
[Email] [varchar](50) NULL,
[IssDate] [varchar](8) NULL,
[IssKind] [varchar](2) NULL,
[IssLocate] [varchar](2) NULL,
[Education] [varchar](50) NULL,
[Career] [varchar](50) NULL,
[Income] [int] NULL,
[RegistCapital] [varchar](30) NULL,
[PaidCapital] [varchar](30) NULL,
[OwnerID] [varchar](20) NULL,
[OwnName] [varchar](50) NULL,
[OwnBirth] [char](8) NULL,
[CompName] [varchar](50) NULL,
[Address_3] [varchar](2) NULL,
[Address_4] [varchar](2) NULL
);
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_CustID] DEFAULT ('') FOR [CustID]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_CustName] DEFAULT ('') FOR [CustName]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_EnCustName] DEFAULT ('') FOR [EnCustName]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_Birth] DEFAULT ('') FOR [Birth]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_CustTel] DEFAULT ('') FOR [CustTel]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_CompTel] DEFAULT ('') FOR [CompTel]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_Mobile] DEFAULT ('') FOR [Mobile]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_AddressArea_1] DEFAULT ('') FOR [AddressArea_1]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_Address_1] DEFAULT ('') FOR [Address_1]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_AddressArea_2] DEFAULT ('') FOR [AddressArea_2]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_Address_2] DEFAULT ('') FOR [Address_2]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_Email] DEFAULT ('') FOR [Email]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_IssDate] DEFAULT ('') FOR [IssDate]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_IssKind] DEFAULT ('') FOR [IssKind]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_IssLocate] DEFAULT ('') FOR [IssLocate]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_Education] DEFAULT ('') FOR [Education]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_Career] DEFAULT ('') FOR [Career]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_Income] DEFAULT ('0') FOR [Income]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_RegisCapital] DEFAULT ('') FOR [RegistCapital]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_PaidCapital] DEFAULT ('') FOR [PaidCapital]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_OwnerID] DEFAULT ('') FOR [OwnerID]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_OwnName] DEFAULT ('') FOR [OwnName]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_OwnBirth] DEFAULT ('') FOR [OwnBirth]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_CompName] DEFAULT ('') FOR [CompName]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_Address_3] DEFAULT ('') FOR [Address_3]
ALTER TABLE [dbo].[SalaryCIF_Tmp] ADD CONSTRAINT [DF_SalaryCIF_Tmp_Address_4] DEFAULT ('') FOR [Address_4]
--判斷檔案是否存在
set nocount on
declare @FileExist int
declare @FileExist_cmd NVARCHAR(MAX)
SET @FileExist_cmd = 'EXEC master.dbo.xp_fileexist ''' + @filename + ''',@FileExistOUT output '
--exec master.dbo.xp_fileexist 'D:\SalaryFile\UploadFile20151110.csv', @FileExist output
EXECUTE sp_executesql
@FileExist_cmd,
N'@FileExistOUT int output',@FileExistOUT=@FileExist OUTPUT
SELECT @FileExist
IF (@FileExist = 1) --1:檔案存在 0:檔案不存在
begin
PRINT '**** 新增資料至暫存的Table = SalaryCIF_Tmp'
SET NOCOUNT ON;
SET @bulk_cmd = 'BULK INSERT SalaryCIF_Tmp '
SET @bulk_cmd += 'FROM ''' + @filename + ''' '
SET @bulk_cmd += 'WITH (FIELDTERMINATOR = '','',ROWTERMINATOR = ''\n'')';
EXEC(@bulk_cmd);
PRINT '**** 從暫存的Table更新至正式的Table = SalaryCIF'
INSERT INTO [dbo].[SalaryCIF]
([CustID],[CustName],[EnCustName],[Birth],[CustTel],[CompTel],[Mobile]
,[AddressArea_1],[Address_1],[AddressArea_2],[Address_2]
,[Email],[IssDate],[IssKind],[IssLocate],[Education],[Career],[Income],[RegistCapital],[PaidCapital],[OwnerID]
,[OwnName],[OwnBirth],[CompName],[Address_3],[Address_4])
SELECT [CustID],[CustName],[EnCustName],[Birth],[CustTel],[CompTel],[Mobile]
,[AddressArea_1],[Address_1],[AddressArea_2],[Address_2]
,[Email],[IssDate],[IssKind],[IssLocate],[Education],[Career],[Income],[RegistCapital],[PaidCapital],[OwnerID]
,[OwnName],[OwnBirth],[CompName],[Address_3],[Address_4]
FROM SalaryCIF_Tmp
PRINT '**** 刪除暫存的Table = SalaryCIF_Tmp'
DROP TABLE SalaryCIF_Tmp;
PRINT '**** 執行完成 = SalaryFileUpload' + convert(char(23),getdate(),21)
PRINT '**** 檔案更名作業'
DECLARE @Time VARCHAR(19)
SELECT @Time=replace(CONVERT(CHAR(8),GETDATE(),108),':','')
set @bulk_cmd = ' master.dbo.xp_cmdshell ''MOVE ' + @filename +' ' + @filename + '_' + @Time + '.csv '''
--PRINT @bulk_cmd
EXEC (@bulk_cmd)
PRINT '**** 檔案更名作業 = ' + @filename + '_' + @Time + '.csv ' + convert(char(23),getdate(),21)
end
else
begin
print '匯入檔案不存在:' + @filename
DROP TABLE SalaryCIF_Tmp;
end
END
/*
BULK
INSERT SalaryCIF
FROM 'D:\\SalaryFile\\UploadFile20150731.csv'
WITH
(
--指定逗號 (,) 作為欄位結束字元。
FIELDTERMINATOR = ',',
--指定資料列結束字元為新行字元。
ROWTERMINATOR = '\n'
)
*/
留言列表
