经典短小代码收集 第二季解决方法

if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([col] int)insert [tb]select 100001 union allselect 100002 union allselect 100003 union allselect 100004 union allselect 100008 union allselect 100012 union allselect 100013 union allselect 100014 union allselect 100023 union allselect 100030 union allselect 100031 union allselect 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)  endfrom #1 a,#2 bwhere a.tid=b.tidselect @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 newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]实例:SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions-------------------------------------------------------------3、将SQL SERVER中查询到的数据导成一个Excel文件-- ======================================================T-SQL代码：EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'参数：S 是SQL服务器名；U是用户；P是密码说明：还可以导出文本文件等多种格式实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'在VB6中应用ADO导出EXCEL文件代码： Dim cn  As New ADODB.Connectioncn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"------------------------------------------------------------4、在SQL SERVER里往Excel插入数据:-- ======================================================insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)T-SQL代码：INSERT INTO  OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  (bestand, produkt) VALUES (20, 'Test')