SQL Server2008R2大批数据导出导入文本文件

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