今天做了几个report,用了不少sql语句,比较集中的记一下:
1. IsNUll(column,‘ 要替换的内容’)。
2. 如果要是ISNotNULL呢?这就用到了CASE WHEN [ADEmailaddress] Is Null THEN ' ' ELSE 'x' END as [Email],
3,复制一个table,backup一个table:
SELECT *
INSERT INTO table1
FROM table2
4, insert one row from one table to another:
INSERT INTO table (f1, f2)
SELECT a, b
FROM table2
WHERE somefield = 'some_value'
如果要insert里面加一些explicit values,那就得先set :
SET IDENTITY_INSERT tableA ON
5, 清空table,delete from Table。
6,清除table,drop Table。
7,添加没有的entry,用到 if else语句:
if (select * from [Webforms].[dbo].[ResHalls] where [HallName]='907 8th St' ) Is null begin
INSERT INTO [Webforms].[dbo].[ResHalls] ([HallName]) SELECT '907 8th St' end