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