USE [ipVA_Builder] GO /* Object: StoredPRocedure [dbo].[usp_ExportOrImport_Data] Script Date: 03/04/2017 20:50:48 */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO – ============================================= – Author: – Create date: – Description: /* EXEC [usp_ExportOrImport_Data] ‘1’, ‘d:/111/’, ‘192.168.1.11’, ‘sa’, ‘123456’ */ – ============================================= ALTER PROCEDURE [dbo].[usp_ExportOrImport_Data] @Type int, –1導出數據,2導入數據 @Path nvarchar(max), –導出導入路徑 @Server nvarchar(100),–服務器IP @Sa nvarchar(50), –數據庫登錄名 @PassWord nvarchar(50)–數據庫密碼 AS BEGIN set nocount on; declare @n int,@count int declare @TablerName nvarchar(100),@DataBaseName nvarchar(100) declare @SQL varchar(max) create table #TableNameA ( ID INT IDENTITY(1,1), Name nvarchar(100) ) insert into #TableNameA select name from sysobjects where xtype=’U’ select @DataBaseName=DB_NAME() select @count=COUNT(*) from #TableNameA set @n=1 while(@n<=@count) begin select @TablerName=Name from #TableNameA where ID=@n if(@TablerName =’Summary_Thirty’ or @TablerName =’Summary_Sixty’ or @TablerName =’Summary_Day’ or @TablerName =’Summary_Week’ or @TablerName =’Summary_Month’ or @TablerName =’Summary_Year’ or @TablerName =’Traffic_CountData’ or @TablerName =’Traffic_CountData_bak’ or @TablerName =’PlazaStoreInfo_ZCZL_temp’) begin print 1 end else begin if(@Type=1) begin set @SQL=’bcp ‘+@DataBaseName+’..’+@TablerName+’ out ‘+@Path+”+@TablerName+’.txt -c -S’+@Server+’ -U’+@Sa+’ -P’+@PassWord+” set @SQL=’master..xp_cmdshell”’+@SQL+”” –PRINT @SQL EXEC(@SQL) end if(@Type=2) begin set @SQL=’bcp ‘+@DataBaseName+’..’+@TablerName+’ in ‘+@Path+”+@TablerName+’.txt -c -E -S’+@Server+’ -U’+@Sa+’ -P’+@PassWord+” set @SQL=’master..xp_cmdshell”’+@SQL+”” –PRINT @SQL EXEC(@SQL) end end set @n=@n+1 end END