复制表结构

select * into qidichuangxingongzhang --new

from ygyzspb2 --old

where 1=2

复制表数据

insert into qidichuangxingongzhang select * from ygyzspb2

快速转换成日期

select convert(char,列名,111) ，convert(char(10),列名,120) from tablename

常用的方法

Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46 Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47 Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49 Select CONVERT(varchar(100), GETDATE(), 12): 110516 Select CONVERT(varchar(100), GETDATE(), 23): 2011-05-16

查询60天之前的数据

select dateadd(d,-60,getdate())

统计单个表 占用空间的大小

exec sp_spaceused 'CustomModule_2013117142926640'

在视图里用 TOP (100) PERCENT 可以用order by 语句

select TOP (100) PERCENT name from gongsiweihu2020 where subgroup='控股' and zt<>'禁用' order by name

order by语句怎么让他自然排序数学字段，而不是1，10，100，2

order by cast( no as int )

去除空格

ltrim(htbh)

中间截取

SUBSTRING(ltrim(htbh),4,8),

添加序号

Row_Number() OVER (partition by faqiren ORDER BY faqiren desc) as 序号 ,

判定表是否存在

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[huayehetongtongzhiviewlinshibiao]') AND type in (N'U')) drop table huayehetongtongzhiviewlinshibiao

select * into huayehetongtongzhiviewlinshibiao from 华业合同通知view

查询存储过程创建的时间

select [name] ,create_date ,modify_date FROM sys.all_objects where type_desc = N'SQL_STORED_PROCEDURE' order by create_date

update

update 待更新表 a

INNER JOIN 关联表 b on a.id=b.id

set a.name=b.name ,a.age=b.age

where b.age > 30