[SQL] 一次新增多筆資料列 SqlServer2005 & SqlServer 2008

2005用法：
Previous method 1:
USE YourDB
GOINSERT INTO MyTable (FirstCol, SecondCol)VALUES ('First',1);INSERT INTO MyTable (FirstCol, SecondCol)VALUES ('Second',2);INSERT INTO MyTable (FirstCol, SecondCol)VALUES ('Third',3);INSERT INTO MyTable (FirstCol, SecondCol)VALUES ('Fourth',4);INSERT INTO MyTable (FirstCol, SecondCol)VALUES ('Fifth',5);GO

Previous method 2:
USE YourDB
GOINSERT INTO MyTable (FirstCol, SecondCol)SELECT 'First' ,1UNION ALLSELECT 'Second' ,2UNION ALLSELECT 'Third' ,3UNION ALLSELECT 'Fourth' ,4UNION ALLSELECT 'Fifth' ,5
GO


CODE：
            sqlQuery = "INSERT into [attend] " & _
            "SELECT @staff_sn ,@ym,'事假',@hr事,'0',@hrY事,'0','0', @CreateDate, @CreateUserID,@CreateUserName,@ModifyDate," & _
            "@ModifyUserID, @ModifyUserName, @SystemModifyDate" & _
            " UNION ALL " & _
            "SELECT @staff_sn ,@ym,'病假',@hr病,'0',@hrY病,'0','0', @CreateDate, @CreateUserID,@CreateUserName,@ModifyDate," & _
            "@ModifyUserID, @ModifyUserName, @SystemModifyDate" & _
            " UNION ALL " & _
            "SELECT @staff_sn ,@ym,'加班',0,'0',@hrY加,@timesY加,@times730加, @CreateDate, @CreateUserID,@CreateUserName,@ModifyDate," & _
            "@ModifyUserID, @ModifyUserName, @SystemModifyDate" & _
            " UNION ALL " & _
            "SELECT @staff_sn ,@ym,'遲到',@hr遲,'0','0',@timesY遲,'0', @CreateDate, @CreateUserID,@CreateUserName,@ModifyDate," & _
            "@ModifyUserID, @ModifyUserName, @SystemModifyDate;"


參考：
SQL Insert Into
SQL UNION
INSERT多筆資料
SQL SERVER – 2008 – Insert Multiple Records Using One Insert Statement – Use of Row Constructor
[MS SQL] [SQL]利用UNION ALL整合統計合併資料
一次更新多筆資料列 SqlServer2005 & SqlServer 2008