1 Excel 直接导入到数据库

exec sp_configure 'show advanced options',1 reconfigure;

exec sp_configure 'Ad Hoc Distributed Queries',1 reconfigure

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

 

 STUFF  函数使用

select zsfz,xsbh=STUFF((select ','+ b.xsbh from Base_xsxx b where b.zsfz=a.zsfz order by zsfz for XML path('')),1,1,'')

from Base_xsxx a

where zslc='D0805'

group by zsfz

 

 

insert into test1

select * ,''

from OPENROWSET('Microsoft.ACE.OLEDB.12.0' ,'Excel 8.0;HDR=YES;DATABASE=E:\南大宿管\南大宿管数据整理Final\东湖\东湖校区06.05修改.xls',sheet1$)

2--查询数据库所有表记录

SELECT a.name, b.rows

FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id

WHERE (a.type = 'u') AND (b.indid IN (0, 1))

ORDER BY b.rows DESC

3 表中某个字段有多条数据，排序后取其中一条

 select WORKER_NUMBER,WORKER_NAME,JOB_ATTHETIME,row_number() over(partition by WORKER_NUMBER order by JOB_ATTHETIME asc) rnn 

from base_PersonnelBA_ManagePositionTime

4 查询表备注信息

SELECT     表名       = case when a.colorder=1 then d.name else '' end,    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,    字段序号   = a.colorder,    字段名     = a.name,    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (                     SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,    类型       = b.name,    占用字节数 = a.length,    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),    允许空     = case when a.isnullable=1 then '√'else '' end,    默认值     = isnull(e.text,''),    字段说明   = isnull(g.[value],'')FROM     syscolumns aleft join     systypes b on     a.xusertype=b.xusertypeinner join     sysobjects d on     a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'left join     syscomments e on     a.cdefault=e.idleft join sys.extended_properties   g on     a.id=G.major_id and a.colid=g.minor_id  left joinsys.extended_properties fon     d.id=f.major_id and f.minor_id=0where     d.name='base_cyxx'  --and a.name='Worker_AddressG'  --如果只查询指定表,加上此红色where条件，tablename是要查询的表名；去除红色where条件查询说有的表信息order by     a.id,a.colorder

5 sql 添加备注

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'学院名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'base_cyxx', @level2type=N'COLUMN',@level2name=N'xymc'

6 查询数据库所有表的记录数

SELECT a.name, b.rows

FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id

WHERE (a.type = 'u') AND (b.indid IN (0, 1))

ORDER BY b.rows DESC

7 字段截取 

substring，charindex

declare @fileName varchar(100)set @fileName='aaa.exe'select substring(@fileName,charindex('.',@fileName)+1,len(@fileName))

 8 开窗函数 

对于排序开窗函数来讲，它支持的开窗函数分别为：ROW_NUMBER（行号）、RANK（排名）、DENSE_RANK（密集排名）和NTILE（分组排名）。

select  FName, FSalary, FCity, FAge,  row_number() over(order by FSalary) as rownum,  rank() over(order by FSalary) as rank,  dense_rank() over(order by FSalary) as dense_rank,  ntile(3) over(order by FSalary)as ntile ,COUNT(FName) OVER(PARTITION BY FCITY) as FCITYcount,COUNT(FName) OVER(PARTITION BY FAGE) as FAGEcountfrom  T_Person order by  FAGEcount --分组排名 ROW_NUMBER()OVER(PARTITION BY [课程] ORDER BY [分数] desc) as id 

 

 跨服务器导入数据

不同服务器数据库之间的数据操作 --创建链接服务器 exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' exec sp_addlinkedsrvlogin  'ITSV ', 'false ',null, '用户名 ', '密码 '  --查询示例 select * from ITSV.数据库名.dbo.表名  --导入示例 select * into 表 from ITSV.数据库名.dbo.表名  --以后不再使用时删除链接服务器 exec sp_dropserver  'ITSV ', 'droplogins '  --连接远程/局域网数据(openrowset/openquery/opendatasource) --1、openrowset  --查询示例 select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)  --生成本地表 select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)  --把本地表导入远程表 insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) select *from 本地表  --更新本地表 update b set b.列A=a.列A  from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b on a.column1=b.column1  --openquery用法需要创建一个连接  --首先创建一个连接创建链接服务器 exec sp_addlinkedserver   'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' --查询 select * FROM openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ') --把本地表导入远程表 insert openquery(ITSV,  'SELECT *  FROM 数据库.dbo.表名 ') select * from 本地表 --更新本地表 update b set b.列B=a.列B FROM openquery(ITSV,  'SELECT * FROM 数据库.dbo.表名 ') as a  inner join 本地表 b on a.列A=b.列A  --3、opendatasource/openrowset SELECT   * FROM   opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta --把本地表导入远程表 insert opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名 select * from  --4。--EXEC AT使用 必须启动rpcEXEC sp_addlinkedserver 'chinancl',' ','SQLOLEDB','CHINANCL-CMS'   --新建连接EXEC sp_addlinkedsrvlogin 'chinancl','false',null,'sa','sa'        --用户名 密码EXEC sp_serveroption 'chinancl','rpc out',true                     --启动RPC EXEC('create database text ') at [chinancl]EXEC(' use text   create table text (id int)') at [chinancl] exec sp_dropserver 'chinancl','droplogins'                          --删除链接登录名