MsSQL话语：一次插入多行，删去重复行，排序。求指导_Sql Server

MsSQL语句：一次插入多行，删去重复行，排序。求指导
初学MsSQL 看到几道题也有了自己的答案不晓得对不对求指导

1。插入100行到下列表中
CREATE TABLE #TEST (TEST_ID INT IDENTITY(1,1))

2。按时间顺序排列下表日期为空的最后显示
CREATE TABLE #TEST (COL1 VARCHAR(10), COL2 SMALLDATETIME NULL)
INSERT INTO #TEST VALUES ('FA','2008-10-01')
INSERT INTO #TEST VALUES ('BB',NULL)
INSERT INTO #TEST VALUES ('CD','2007-05-20')
INSERT INTO #TEST VALUES ('AA',NULL)
INSERT INTO #TEST VALUES ('XZ','2008-10-01')

3. Single DELETE query 删去重复行

CREATE TABLE #TEST (ID TINYINT NULL, COL1 CHAR(1))
INSERT INTO #TEST (ID,COL1) VALUES (1,'A')
INSERT INTO #TEST (ID,COL1) VALUES (2,'B')
INSERT INTO #TEST (ID,COL1) VALUES (1,'A')
INSERT INTO #TEST (ID,COL1) VALUES (1,'B')

------解决方案--------------------

SQL code

--1INSERT INTO #test DEFAULT VALUESGO 100
------解决方案--------------------
SQL code  、--2SELECT * FROM #TEST ORDER BY CASE WHEN COL2 IS NULL THEN 1 ELSE 0 END,COL1
------解决方案--------------------
SQL code--3; WITH cte AS (SELECT *,row_id=ROW_NUMBER()OVER(PARTITION BY ID,COL1 ORDER BY ID) FROM #TEST )DELETE FROM cte WHERE row_id>1SELECT * FROM #TEST