精妙的SQL和SQL SERVER 与ACCESS、EXCEL的数据导入导出转换

  1 * 说明：复制表(只复制结构,源表名：a 新表名：b)
  2       select * into b from a where 1<>1
  3 
  4     * 说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
  5       insert into b(a, b, c) select d,e,f from b;
  6 
  7     * 说明：显示文章、提交人和最后回复时间
  8       select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
  9 
 10     * 说明：外连接查询(表名1：a 表名2：b)
 11       select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
 12 
 13     * 说明：日程安排提前五分钟提醒
 14       select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
 15 
 16     * 说明：两张关联表，删除主表中已经在副表中没有的信息
 17       delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
 18 
 19     * 说明：--
 20 
 21       SQL:
 22 
 23       SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
 24 
 25       FROM TABLE1,
 26 
 27       (SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
 28 
 29       FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
 30 
 31       FROM TABLE2
 32 
 33       WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,
 34 
 35       (SELECT NUM, UPD_DATE, STOCK_ONHAND
 36 
 37       FROM TABLE2
 38 
 39       WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =
 40 
 41       TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,
 42 
 43       WHERE X.NUM = Y.NUM （+）
 44 
 45       AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
 46 
 47       WHERE A.NUM = B.NUM
 48 
 49     * 说明：--
 50       select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩
 51 
 52     * 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
 53 
 54       SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,
 55 
 56       SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,
 57 
 58       SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,
 59 
 60       SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,
 61 
 62       SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,
 63 
 64       SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,
 65 
 66       SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,
 67 
 68       SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,
 69 
 70       SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,
 71 
 72       SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,
 73 
 74       SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,
 75 
 76       SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,
 77 
 78       SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC
 79 
 80       FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
 81 
 82       FROM TELFEESTAND a, TELFEE b
 83 
 84       WHERE a.tel = b.telfax) a
 85 
 86       GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
 87 
 88     * 说明：四表联查问题
 89       select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..
 90 
 91     * 说明：得到表中最小的未使用的ID号
 92 
 93     * SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID  FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
 94 
 95     * 一个SQL语句的问题:行列转换
 96       select * from v_temp
 97       上面的视图结果如下:
 98       user_name role_name
 99       -------------------------
100       系统管理员 管理员
101       feng 管理员
102       feng 一般用户
103       test 一般用户
104       想把结果变成这样:
105       user_name role_name
106       ---------------------------
107       系统管理员 管理员
108       feng 管理员,一般用户
109       test 一般用户
110       ===================
111       create table a_test(name varchar(20),role2 varchar(20))
112       insert into a_test values('李','管理员')
113       insert into a_test values('张','管理员')
114       insert into a_test values('张','一般用户')
115       insert into a_test values('常','一般用户')
116 
117       create function join_str(@content varchar(100))
118       returns varchar(2000)
119       as
120       begin
121       declare @str varchar(2000)
122       set @str=''
123       select @str=@str+','+rtrim(role2) from a_test where [name]=@content
124       select @str=right(@str,len(@str)-1)
125       return @str
126       end
127       go
128 
129       --调用：
130       select [name],dbo.join_str([name]) role2 from a_test group by [name]
131 
132       --select distinct name,dbo.uf_test(name) from a_test
133 
134     * 快速比较结构相同的两表
135       结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？
136       ============================
137       给你一个测试方法，从northwind中的orders表取数据。
138       select * into n1 from orders
139       select * into n2 from orders
140 
141       select * from n1
142       select * from n2
143 
144       --添加主键，然后修改n1中若干字段的若干条
145       alter table n1 add constraint pk_n1_id primary key (OrderID)
146       alter table n2 add constraint pk_n2_id primary key (OrderID)
147 
148       select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1
149 
150       应该可以，而且将不同的记录的ID显示出来。
151       下面的适用于双方记录一样的情况，
152 
153       select * from n1 where orderid in (select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1)
154       至于双方互不存在的记录是比较好处理的
155       --删除n1,n2中若干条记录
156       delete from n1 where orderID in ('10728','10730')
157       delete from n2 where orderID in ('11000','11001')
158 
159       --*************************************************************
160       -- 双方都有该记录却不完全相同
161       select * from n1 where orderid in(select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1)
162       union
163       --n2中存在但在n1中不存的在10728,10730
164       select * from n1 where OrderID not in (select OrderID from n2)
165       union
166       --n1中存在但在n2中不存的在11000,11001
167       select * from n2 where OrderID not in (select OrderID from n1)
168 
169     * 四种方法取表里n到m条纪录：
170 
171       1.
172       select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
173       set rowcount n
174       select * from 表变量 order by columnname desc
175 
176 
177       2.
178       select top n * from (select top m * from tablename order by columnname) a order by columnname desc
179 
180 
181       3.如果tablename里没有其他identity列，那么：
182       select identity(int) id0,* into #temp from tablename
183 
184       取n到m条的语句为：
185       select * from #temp where id0 >=n and id0 <= m
186 
187       如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：
188       exec sp_dboption 你的DB名字,'select into/bulkcopy',true
189 
190 
191       4.如果表里有identity属性，那么简单：
192       select * from tablename where identitycol between n and m
193 
194     * 如何删除一个表中重复的记录？
195       create table a_dist(id int,name varchar(20))
196 
197       insert into a_dist values(1,'abc')
198       insert into a_dist values(1,'abc')
199       insert into a_dist values(1,'abc')
200       insert into a_dist values(1,'abc')
201 
202       exec up_distinct 'a_dist','id'
203 
204       select * from a_dist
205 
206       create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
207       --f_key表示是分组字段﹐即主键字段
208       as
209       begin
210       declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
211       select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'
212       exec(@sql)
213       open cur_rows
214       fetch cur_rows into @id,@max
215       while @@fetch_status=0
216       begin
217       select @max = @max -1
218       set rowcount @max
219       select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
220       if @type=56
221       select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id
222       if @type=167
223       select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''
224       exec(@sql)
225       fetch cur_rows into @id,@max
226       end
227       close cur_rows
228       deallocate cur_rows
229       set rowcount 0
230       end
231 
232       select * from systypes
233       select * from syscolumns where id = object_id('a_dist')
234 
235     * 查询数据的最大排序问题（只能用一条语句写）
236       CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))
237 
238       insert into hard values ('A','1',3)
239       insert into hard values ('A','2',4)
240       insert into hard values ('A','4',2)
241       insert into hard values ('A','6',9)
242       insert into hard values ('B','1',4)
243       insert into hard values ('B','2',5)
244       insert into hard values ('B','3',6)
245       insert into hard values ('C','3',4)
246       insert into hard values ('C','6',7)
247       insert into hard values ('C','2',3)
248 
249 
250       要求查询出来的结果如下：
251 
252       qu co je
253       ----------- ----------- -----
254       A 6 9
255       A 2 4
256       B 3 6
257       B 2 5
258       C 6 7
259       C 3 4
260 
261 
262       就是要按qu分组，每组中取je最大的前2位！！
263       而且只能用一句sql语句！！！
264       select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)
265 
266     * 求删除重复记录的sql语句？
267       怎样把具有相同字段的纪录删除，只留下一条。
268       例如，表test里有id,name字段
269       如果有name相同的记录 只留下一条，其余的删除。
270       name的内容不定，相同的记录数不定。
271       有没有这样的sql语句？
272       ==============================
273       A:一个完整的解决方案：
274 
275       将重复的记录记入temp1表:
276       select [标志字段id],count(*) into temp1 from [表名]
277       group by [标志字段id]
278       having count(*)>1
279 
280       2、将不重复的记录记入temp1表:
281       insert temp1 select [标志字段id],count(*) from [表名] group by [标志字段id] having count(*)=1
282 
283       3、作一个包含所有不重复记录的表：
284       select * into temp2 from [表名] where 标志字段id in(select 标志字段id from temp1)
285 
286       4、删除重复表:
287       delete [表名]
288 
289       5、恢复表：
290       insert [表名] select * from temp2
291 
292       6、删除临时表:
293       drop table temp1
294       drop table temp2
295       ================================
296       B:
297       create table a_dist(id int,name varchar(20))
298 
299       insert into a_dist values(1,'abc')
300       insert into a_dist values(1,'abc')
301       insert into a_dist values(1,'abc')
302       insert into a_dist values(1,'abc')
303 
304       exec up_distinct 'a_dist','id'
305 
306       select * from a_dist
307 
308       create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
309       --f_key表示是分组字段﹐即主键字段
310       as
311       begin
312       declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
313       select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'
314       exec(@sql)
315       open cur_rows
316       fetch cur_rows into @id,@max
317       while @@fetch_status=0
318       begin
319       select @max = @max -1
320       set rowcount @max
321       select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
322       if @type=56
323       select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id
324       if @type=167
325       select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''
326       exec(@sql)
327       fetch cur_rows into @id,@max
328       end
329       close cur_rows
330       deallocate cur_rows
331       set rowcount 0
332       end
333 
334       select * from systypes
335       select * from syscolumns where id = object_id('a_dist')
336 
337     * 行列转换--普通
338 
339       假设有张学生成绩表(CJ)如下
340       Name Subject Result
341       张三 语文 80
342       张三 数学 90
343       张三 物理 85
344       李四 语文 85
345       李四 数学 92
346       李四 物理 82
347 
348       想变成
349       姓名 语文 数学 物理
350       张三 80 90 85
351       李四 85 92 82
352 
353       declare @sql varchar(4000)
354       set @sql = 'select Name'
355       select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
356       from (select distinct Subject from CJ) as a
357       select @sql = @sql+' from test group by name'
358       exec(@sql)
359 
360       行列转换--合并
361 
362       有表A,
363       id pid
364       1 1
365       1 2
366       1 3
367       2 1
368       2 2
369       3 1
370       如何化成表B:
371       id pid
372       1 1,2,3
373       2 1,2
374       3 1
375 
376       创建一个合并的函数
377       create function fmerg(@id int)
378       returns varchar(8000)
379       as
380       begin
381       declare @str varchar(8000)
382       set @str=''
383       select @str=@str+','+cast(pid as varchar) from 表A where id=@id
384       set @str=right(@str,len(@str)-1)
385       return(@str)
386       End
387       go
388 
389       --调用自定义函数得到结果
390       select distinct id,dbo.fmerg(id) from 表A
391 
392     * 如何取得一个数据表的所有列名
393 
394       方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
395       SQL语句如下：
396       declare @objid int,@objname char(40)
397       set @objname = 'tablename'
398       select @objid = id from sysobjects where id = object_id(@objname)
399       select 'Column_name' = name from syscolumns where id = @objid order by colid
400 
401       或
402 
403       SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='users'
404 
405     * 通过SQL语句来更改用户的密码
406 
407       修改别人的,需要sysadmin role
408       EXEC sp_password NULL, 'newpassword', 'User'
409 
410       如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa
411 
412     * 怎么判断出一个表的哪些字段不允许为空？
413 
414       select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename
415 
416     * 如何在数据库里找到含有相同字段的表？
417       a. 查已知列名的情况
418       SELECT b.name as TableName,a.name as columnname
419       From syscolumns a INNER JOIN sysobjects b
420       ON a.id=b.id
421       AND b.type='U'
422       AND a.name='你的字段名字'
423 
424     * 未知列名查所有在不同表出现过的列名
425       Select o.name As tablename,s1.name As columnname
426       From syscolumns s1, sysobjects o
427       Where s1.id = o.id
428       And o.type = 'U'
429       And Exists (
430       Select 1 From syscolumns s2
431       Where s1.name = s2.name
432       And s1.id <> s2.id
433       )
434 
435     * 查询第xxx行数据
436 
437       假设id是主键：
438       select * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)
439 
440       如果使用游标也是可以的
441       fetch absolute [number] from [cursor_name]
442       行数为绝对行数
443 
444     * SQL Server日期计算
445       a. 一个月的第一天
446       SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
447       b. 本周的星期一
448       SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
449       c. 一年的第一天
450       SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
451       d. 季度的第一天
452       SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
453       e. 上个月的最后一天
454       SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
455       f. 去年的最后一天
456       SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
457       g. 本月的最后一天
458       SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
459       h. 本月的第一个星期一
460       select DATEADD(wk, DATEDIFF(wk,0,
461       dateadd(dd,6-datepart(day,getdate()),getdate())
462       ), 0)
463       i. 本年的最后一天
464       SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
465 
466     * 获取表结构[把 'sysobjects' 替换 成 'tablename' 即可]
467 
468       SELECT CASE IsNull(I.name, '')
469       When '' Then ''
470       Else '*'
471       End as IsPK,
472       Object_Name(A.id) as t_name,
473       A.name as c_name,
474       IsNull(SubString(M.text, 1, 254), '') as pbc_init,
475       T.name as F_DataType,
476       CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '')
477       WHEN '' Then Cast(A.prec as varchar)
478       ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar)
479       END as F_Scale,
480       A.isnullable as F_isNullAble
481       FROM Syscolumns as A
482       JOIN Systypes as T
483       ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') )
484       LEFT JOIN ( SysIndexes as I
485       JOIN Syscolumns as A1
486       ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) )
487       ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) )
488       LEFT JOIN SysComments as M
489       ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 )
490       ORDER BY A.Colid ASC
491 
492     * 提取数据库内所有表的字段详细说明的SQL语句
493 
494       SELECT
495       (case when a.colorder=1 then d.name else '' end) N'表名',
496       a.colorder N'字段序号',
497       a.name N'字段名',
498       (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''
499       end) N'标识',
500       (case when (SELECT count(*)
501       FROM sysobjects
502       WHERE (name in
503       (SELECT name
504       FROM sysindexes
505       WHERE (id = a.id) AND (indid in
506       (SELECT indid
507       FROM sysindexkeys
508       WHERE (id = a.id) AND (colid in
509       (SELECT colid
510       FROM syscolumns
511       WHERE (id = a.id) AND (name = a.name))))))) AND
512       (xtype = 'PK'))>0 then '√' else '' end) N'主键',
513       b.name N'类型',
514       a.length N'占用字节数',
515       COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
516       isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
517       (case when a.isnullable=1 then '√'else '' end) N'允许空',
518       isnull(e.text,'') N'默认值',
519       isnull(g.[value],'') AS N'字段说明'
520       FROM syscolumns a
521       left join systypes b
522       on a.xtype=b.xusertype
523       inner join sysobjects d
524       on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
525       left join syscomments e
526       on a.cdefault=e.id
527       left join sysproperties g
528       on a.id=g.id AND a.colid = g.smallid
529       order by object_name(a.id),a.colorder
530 
531     * 快速获取表test的记录总数[对大容量表非常有效]
532 
533       快速获取表test的记录总数:
534       select rows from sysindexes where id = object_id('test') and indid in (0,1)
535 
536       update 2 set KHXH=(ID+1)\2 2行递增编号
537       update [23] set id1 = 'No.'+right('00000000'+id,6) where id not like 'No%' //递增
538       update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6) //补位递增
539       delete from [1] where (id%2)=1
540       奇数
541 
542     * 替换表名字段
543       update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/') where domurl like '%Upload/Imgswf/%'
544 
545     * 截位
546       SELECT LEFT(表名, 5)
547 熟悉SQL SERVER 2000的数据库管理员都知道，其DTS可以进行数据的导入导出，其实，我们也可以使用Transact-SQL语句进行导入导出操作。在 Transact-SQL语句中，我们主要使用OpenDataSource函数、OPENROWSET 函数，关于函数的详细说明，请参考SQL联机帮助。利用下述方法，可以十分容易地实现SQL SERVER、ACCESS、EXCEL数据转换，详细说明如下：
548 
549 一、SQL SERVER 和ACCESS的数据导入导出
550 
551 常规的数据导入导出：
552 使用DTS向导迁移你的Access数据到SQL Server，你可以使用这些步骤:
553 　　○1在SQL SERVER企业管理器中的Tools（工具）菜单上，选择Data Transformation
554 　　○2Services（数据转换服务），然后选择  czdImport Data（导入数据）。
555 　　○3在Choose a Data Source（选择数据源）对话框中选择Microsoft Access as the Source，然后键入你的.mdb数据库(.mdb文件扩展名)的文件名或通过浏览寻找该文件。
556 　　○4在Choose a Destination（选择目标）对话框中，选择Microsoft OLE　DB Prov ider for SQL　Server，选择数据库服务器，然后单击必要的验证方式。
557 　　○5在Specify Table Copy（指定表格复制）或Query（查询）对话框中，单击Copy tables（复制表格）。
558     ○6在Select Source Tables（选择源表格）对话框中，单击Select All（全部选定）。下一步，完成。
559 
560 Transact-SQL语句进行导入导出：
561 1.在SQL SERVER里查询access数据:
562 
563 SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\DB.mdb";User ID=Admin;Password=')表名
564 
565 2.将access导入SQL server
566 在SQL SERVER 里运行:
567 SELECT * INTO newtable FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="c:\DB.mdb";User ID=Admin;Password=' )表名
568 
569 3.将SQL SERVER表里的数据插入到Access表中
570 在SQL SERVER 里运行：
571 insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=" c:\DB.mdb";User ID=Admin;Password=')表名 (列名1,列名2) select 列名1,列名2  from  sql表
572 
573 实例：
574 insert into  OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\db.mdb';'admin';'', Test) select id,name from Test
575 
576 
577 INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', 表名) SELECT * FROM sqltablename
578 
579 二、SQL SERVER 和EXCEL的数据导入导出
580 
581 1、在SQL SERVER里查询Excel数据:
582 
583 SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')[Sheet1$]
584 
585 下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
586 SELECT * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')xactions
587 
588 2、将Excel的数据导入SQL server :
589 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$]
590 
591 实例:
592 SELECT * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')xactions
593 
594 3、将SQL SERVER中查询到的数据导成一个Excel文件
595 T-SQL代码：
596 EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
597 参数：S 是SQL服务器名；U是用户；P是密码
598 说明：还可以导出文本文件等多种格式
599 
600 实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
601 
602 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'
603 
604 在VB6中应用ADO导出EXCEL文件代码：
605 Dim cn  As New ADODB.Connection
606 cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
607 cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
608 
609 
610 4、在SQL SERVER里往Excel插入数据:
611 
612 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)
613 
614 T-SQL代码：
615 INSERT INTO 
616  OPENDATASOURCE('Microsoft.JET.OLEDB.4.0','Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')[Filiale1$]  (bestand, produkt) VALUES (20, 'Test') 
617 
618 
619 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/jshang/archive/2007/12/14/1935772.aspx