1.批量导入
INSERT INTO Table2(field1,field2,...) SELECT value1,value2,... FROMTable1
要求目标表Table2必须存在
2.批量导入
SELECT vale1, value2 INTO Table2 FROM Table1
要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中
3.去重并保留一项
DELETE FROM TableName WHERE ColName IN (select ColName
FROM TableName GROUP BY ColName HAVING COUNT(ColName) > 1)
AND id NOT in( select MAX(id) FROM ColName GROUP BY ProductId HAVING COUNT(ColName) > 1)
4.批量更新
UPDATE t1
SET Field1 = Field21, Field2 = Field22
FROM Table2 t2
WHERE ((t2.Field24 >= '') AND t1.fId = t4.fId);
5.分页
--max/top
SELECT TOP * FROM pagetest
WHERE id>(SELECT MAX(id) FROM (SELECT TOP id FROM pagetest ORDER BY id)a)
ORDER BY id
--row_number()
SELECT * FROM
(SELECT ROW_NUMBER()OVER(ORDER BY id)rownumber,* FROM pagetest)a
WHERE rownumber>= and rownumber<=9952
-- OFFSET FETCH (需要sql2012以上)
SELECT * FROM pagetest
ORDER BY id ASC
OFFSET @pageIndex * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY
6. 表同步 MERGE
MERGE into [MergeDemo] as m -- 目标表
using Test as t -- 源表
on m.[title]=t.[name] -- 匹配条件
when matched then update set m.[num]=t.[age] -- 匹配成功
when not matched then insert values(t.[name],t.[age]) -- 目标表与源表不一致
when not matched by source then delete -- 源表与目标表不一致
output $action as [action] -- 输出操作
, inserted.[title] [ititle],inserted.[num] [inum]
, deleted.[title] [dtitle],deleted.[num] [dnum];
7. GROUP 加 TOP（每个 GROUP 取出 TOP 数据）
Declare @currentIndex int -- 游标
Declare @totalRows int -- 总的组数
Declare @time nvarchar() -- 第一个分组列
SET @currentIndex=
-- 按 第一个分组列 分组， 取出组名
SELECT row_number()over(order by [time] asc) id,[time] INTO #temp1
FROM (SELECT DISTINCT(CONVERT(VARCHAR(),create_time,)) [time] FROM tableName) a
SELECT @totalRows=COUNT() FROM #temp1 -- 总的组数
WHILE @currentIndex<=@totalRows -- 按 组名 循环
BEGIN
SELECT @time=[time] FROM #temp1 WHERE id=@currentIndex -- 取出当前组名
-- 取出当前 组 拥有的数据， 再按照 第二分组列 分组 取 TOP
SELECT TOP colName 'Url', COUNT((colName)) 'Count',CONVERT(VARCHAR(),create_time,) 'Time',GETDATE()
FROM tableName WHERE [time] = @time GROUP BY colName ORDER BY COUNT((colName)) DESC
SET @currentIndex =@currentIndex+1
END
