對大數(shù)據(jù)量導(dǎo)入導(dǎo)出就好的辦法，就是在命令行中使用bcp命令來導(dǎo)入導(dǎo)出數(shù)據(jù)。

/*MSsql2005以上 如何啟用xp_cmdshell
默認(rèn)情況下,sql server2005以上安裝完后,xp_cmdshell是禁用的(可能是安全考慮),如果要使用它,可按以下步驟
*/

-- 允許配置高級選項
EXEC sp_configure 'show advanced options', 1
GO

-- 啟用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO

/*執(zhí)行內(nèi)容

-- 把 YTHR 中的Employee 表的數(shù)據(jù)導(dǎo)出到D:\estTemp.xls(或d:\TEST.txt） , 導(dǎo)出以后會覆蓋原有文件的內(nèi)容

-- 設(shè)置字段分隔符和行分隔符(-c -t"," -r"\n"),不想輸入字段類型等請配合-c一起使用

exec master..xp_cmdshell'bcp " select * from [Test].[dbo].[employee]" queryout d:\TEST.txt -c -T -t" , " -r"\n"'

exec master..xp_cmdshell'bcp " select DEPTID AS 數(shù)量,salary as 單價,cood as 名稱 from [Test].[dbo].[employee]" queryout d:\estTemp.xls -c -T -q'

--1.把表字段轉(zhuǎn)為中文和數(shù)據(jù)一起導(dǎo)出

go
CREATE TABLE product(productid int,productname varchar(50)) 
insert into product
select 1,'衣服' union
select 2,'帽子' union
select 3,'鞋子' union
select 4,'褲子' union
select 5,'襪子'

declare @count int,@i int,@name varchar(50),@t int
declare @sql varchar(500)

declare @DbTable varchar(100)
SET @DbTable = 'tempTable'
IF EXISTS (SELECT 1
FROM sysobjects
WHERE name = @DbTable
AND TYPE = 'u')
begin
PRINT 'EXISTS '
end;
ELSE
begin
exec('CREATE TABLE tempTable(id int default(''1''))')
exec('insert into tempTable values(''1'')')
PRINT 'NOT EXISTS '
end;

select name,colid from syscolumns where id=object_id('product')
select @count= COUNT(*) from syscolumns where id=object_id('product')
set @i=@count
if @count>0
begin
while @i>0
begin
select @name=name from syscolumns where id=object_id('product') and colid=@i
select @t=count(*) from syscolumns where id=object_id('temptable') and name=@name
if @t=0
begin
set @sql= 'alter table temptable add'+' ' + @name +' ' +'varchar(50)';
print (@sql)
exec(@sql)
set @i=@i-1
--exec(@sql)
--update tempTable set @name="'"+@name+"'" where id='1'
set @sql='update tempTable set ' +@name+'= '''+@name+''' where id=''1'''
print (@sql)
exec(@sql)
end
else
begin
print '123'
return;
end
end
set @count=@count-1
begin
exec master..xp_cmdshell'bcp "select CASE when productid=''productid'' then ''數(shù)量'' END,CASE when productname=''productname'' then ''名稱'' END from [Test].[dbo].[tempTable] union all select ltrim(productid) as productid,productname from [Test].[dbo].[product]" queryout d:\estTemp888.xls -c -T -q'
end;
end;

 -- 2.把表字段內(nèi)容和數(shù)據(jù)一起導(dǎo)出

declare @sql varchar(100)

select @sql=isnull(@sql+',''','''')+name+'''' from syscolumns where id=object_id('product')

exec('select '+@sql+' union all select ltrim(productid),productname from product')

*/

--用完后,要記得將xp_cmdshell禁用(出于安全考慮)

-- 禁用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO