日期:2014-5-19 浏览次数:20374次

经典短小代码收集 第二季
【SQL Server 数据库导入导出部分】

1、在查询分析器下查询Excel文档
SELECT * FROM 
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\测试.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$

2、从数据库中导出数据并存到文件中
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.xls -c -q -S"." -U"sa" -P""'
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.txt -c -q -S"." -U"sa" -P""'

3、从文件中导入数据到数据库对应表中
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN in c:\temp1.xls -c -q -S"." -U"sa" -P""'
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN in c:\temp1.txt -c -q -S"." -U"sa" -P""'


------解决方案--------------------
SQL code

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select 100001 union all
select 100002 union all
select 100003 union all
select 100004 union all
select 100008 union all
select 100012 union all
select 100013 union all
select 100014 union all
select 100023 union all
select 100030 union all
select 100031 union all
select 100032
 
---查询---
select col,tid=identity(int,1,1) into #1 from [tb] t where not exists(select * from tb where col=t.col-1)
select col,tid=identity(int,1,1) into #2 from [tb] t where not exists(select * from tb where col=t.col+1)

declare @result varchar(1000)
select 
  @result=isnull(@result+',','')+
  case 
    when a.col=b.col then ltrim(a.col)
    else ltrim(a.col)+'-'+ltrim(b.col)
  end
from #1 a,#2 b
where a.tid=b.tid

select @result

---结果---
                                                                                    
------------------------------------------
100001-100004,100008,100012-100014,100023,100030-100032

(所影响的行数为 1 行)

------解决方案--------------------
引用楼主 llt_2006 的帖子:
【SQL Server 数据库导入导出部分】

1、在查询分析器下查询Excel文档
SELECT * FROM
OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\测试.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$

2、从数据库中导出数据并存到文件中
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.xls -c -q -S"." -U"sa" -P""'
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN out c:\temp1.txt -c -q -S"." -U"sa" -P""'

3、从文件中导入数据到数据库对应表中
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN in c:\temp1.xls -c -q -S"." -U"sa" -P""'
EXEC master..xp_cmdshell 'bcp CAS2004..HGZ_LIAOJIAN in c:\temp1.txt -c -q -S"." -U"sa" -P""'

------解决方案--------------------
SQL code

二、SQL SERVER 和EXCEL的数据导入导出
1、在SQL SERVER里查询Excel数据:
-- ======================================================
SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT * 
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------

2、将Excel的数据导入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=