Sql Server常用语句

SQL常用语句

1.with语句
WITH AA as
(
select A.UID as correctUID,A.CustomerKey AS CORRECTKEY, b.UID as wrongUID,B.CustomerKey AS wrongkey from A
join B
on SUBSTRING(a.CustomerKey,2,LEN(a.CustomerKey)) = b.CustomerKey
)
select * from AA
JOIN BB
ON AA.wrongUID = BB.CUSTOMERUID

2.从一个表抓数据到另一个表中
假如该表中有自增字段，则该自增字段不用写在语句中
USE dbname
GO
insert into table ([UID]
,[PartUID])
(
SELECT [UID]
,[PartUID]
FROM table
)
GO
或
USE dbname
GO
insert into table ([UID]
,[PartUID])
(
SELECT newid()
,‘xxx’
FROM table where [PartUID] = ‘xx’
)
GO
3.insert语句
insert into table(column1, CreateDate)
VALUES(‘xxx’,GETDATE())
4.给表加字段
alter table tablexxx add columnxxx datetime2(7) ;
5.给表删除字段
alter table tablexxx drop column columnxxx;