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

fateame

　　* 快速比较结构相同的两表
      结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？
      ============================
      给你一个测试方法，从northwind中的orders表取数据。
      select * into n1 from orders
      select * into n2 from orders

      select * from n1
      select * from n2

      --添加主键，然后修改n1中若干字段的若干条
      alter table n1 add constraint pk_n1_id primary key (OrderID)
      alter table n2 add constraint pk_n2_id primary key (OrderID)

      select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1

      应该可以，而且将不同的记录的ID显示出来。
      下面的适用于双方记录一样的情况，

      select * from n1 where orderid in (select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1)
      至于双方互不存在的记录是比较好处理的
      --删除n1,n2中若干条记录
      delete from n1 where orderID in ('10728','10730')
      delete from n2 where orderID in ('11000','11001')

      --*************************************************************
      -- 双方都有该记录却不完全相同
      select * from n1 where orderid in(select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1)
      union
      --n2中存在但在n1中不存的在10728,10730
      select * from n1 where OrderID not in (select OrderID from n2)
      union
      --n1中存在但在n2中不存的在11000,11001
      select * from n2 where OrderID not in (select OrderID from n1)

    * 四种方法取表里n到m条纪录：

      1.
      select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
      set rowcount n
      select * from 表变量 order by columnname desc


      2.
      select top n * from (select top m * from tablename order by columnname) a order by columnname desc


      3.如果tablename里没有其他identity列，那么：
      select identity(int) id0,* into #temp from tablename

      取n到m条的语句为：
      select * from #temp where id0 >=n and id0 <= m

      如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：
      exec sp_dboption 你的DB名字,'select into/bulkcopy',true


      4.如果表里有identity属性，那么简单：
      select * from tablename where identitycol between n and m

    * 如何删除一个表中重复的记录？
      create table a_dist(id int,name varchar(20))

      insert into a_dist values(1,'abc')
      insert into a_dist values(1,'abc')
      insert into a_dist values(1,'abc')
      insert into a_dist values(1,'abc')

      exec up_distinct 'a_dist','id'

      select * from a_dist

      create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
      --f_key表示是分组字段﹐即主键字段
      as
      begin
      declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
      select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'
      exec(@sql)
      open cur_rows
      fetch cur_rows into @id,@max
      while @@fetch_status=0
      begin
      select @max = @max -1
      set rowcount @max
      select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
      if @type=56
      select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id
      if @type=167
      select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''
      exec(@sql)
      fetch cur_rows into @id,@max
      end
      close cur_rows
      deallocate cur_rows
      set rowcount 0
      end

      select * from systypes
      select * from syscolumns where id = object_id('a_dist')
* 行列转换--普通

      假设有张学生成绩表(CJ)如下
      Name Subject Result
      张三 语文 80
      张三 数学 90
      张三 物理 85
      李四 语文 85
      李四 数学 92
      李四 物理 82

      想变成
      姓名 语文 数学 物理
      张三 80 90 85
      李四 85 92 82

      declare @sql varchar(4000)
      set @sql = 'select Name'
      select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
      from (select distinct Subject from CJ) as a
      select @sql = @sql+' from test group by name'
      exec(@sql)

      行列转换--合并

      有表A,
      id pid
      1 1
      1 2
      1 3
      2 1
      2 2
      3 1
      如何化成表B:
      id pid
      1 1,2,3
      2 1,2
      3 1

      创建一个合并的函数
      create function fmerg(@id int)
      returns varchar(8000)
      as
      begin
      declare @str varchar(8000)
      set @str=''
      select @str=@str+','+cast(pid as varchar) from 表A where id=@id
      set @str=right(@str,len(@str)-1)
      return(@str)
      End
      go

      --调用自定义函数得到结果
      select distinct id,dbo.fmerg(id) from 表A

  * 快速获取表test的记录总数[对大容量表非常有效]

      快速获取表test的记录总数:
      select rows from sysindexes where id = object_id('test') and indid in (0,1)

      update 2 set KHXH=(ID+1)\2 2行递增编号
      update [23] set id1 = 'No.'+right('00000000'+id,6) where id not like 'No%' //递增
      update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6) //补位递增
      delete from [1] where (id%2)=1
      奇数