因为每段SQL查询到的资料是下一段查询语句的条件，再加上必须跑回圈去执行？
边查资料边插入资料。

1. mySQL = "Select MAX(ProjectNo) AS MaxProjectNo From tblProject Where ProjectNo Like '" & strPrefix & strSerialYearMonth & "%'" '(取得ProjectNo (Prefix + YYYYMM + SSS 共10码, eg: S201109001))

2. mySQL1 = "SELECT ROW_NUMBER() OVER (ORDER BY C.CateMainID) AS RowNo,A.ProjectNo FROM tblProjectDevpHistory AS A "
   mySQL1 += "INNER JOIN tblProject AS B ON A.ProjectNo = B.ProjectNo "
   mySQL1 += "INNER JOIN tblBasCategory AS C ON B.CateMainID = C.CateMainID "
   mySQL1 += "INNER JOIN tblProject AS B ON A.ProjectNo = B.ProjectNo "
   mySQL1 += "WHERE A.DHDate >= '2020/05/01' And A.DHDate <= '2020/05/31' And A.ProjectNo < 'S2020' "
   mySQL1 += "GROUP BY A.ProjectNo,C.CateMainID" '(取得ProjectNo List 2020/05/01 ~ 2020/05/31) - 共10多笔

3. mySQL2 += "SELECT A.CateMainID,A.CateSubID,A.ProjectName,A.KickOffDate,A.PlanDate,A.DepartmentNo,A.CreatorNo "
   mySQL2 += "FROM tblProject AS A "
   mySQL2 += "INNER JOIN tblBasCategory AS B ON A.CateMainID = B.CateMainID "
   mySQL2 += "INNER JOIN tblProjectItem AS C ON A.ProjectNo = C.ProjectNo "
   mySQL2 += "INNER JOIN tblProjectDevpHistory AS D ON A.ProjectNo = D.ProjectNo AND C.ItemNo = D.ItemNo AND D.DHDate >= '2020/05/01' AND D.DHDate <= '2020/05/31' "
   mySQL2 += "WHERE A.ProjectNo = '" & myDR("ProjectNo").ToString & "' AND A.ProjectNo < 'S2020' "
   mySQL2 += "GROUP BY A.CateMainID,A.CateSubID,A.ProjectName,A.KickOffDate,A.PlanDate,A.DepartmentNo,A.CreatorNo" '(依据2取得的ProjectNo查询Project基本资料)

4. mySQL = "Insert Into tblProject (ProjectNo, CateMainID, CateSubID, ProjectName, KickOffDate, PlanDate, DepartmentNo, CreatorNo) "
   mySQL += "Values ('" & strNewProjectNo & "','" & myDR("CateMainID").ToString & "','" & myDR("CateSubID").ToString & "','" & myDR("ProjectName").ToString & "','" & myDR("KickOffDate").ToString & "','" & myDR("PlanDate").ToString & "','"
            & myDR("DepartmentNo").ToString & "','" & myDR("CreatorNo").ToString & "') "

5. mySQL3 += "SELECT A.ItemNo,A.ItemName,A.DepartmentNo,A.PropertyDesc,A.KickOffDate,A.PlanDate,A.EngineerNo,A.AdvisorNo,A.CreatorNo "
   mySQL3 += "FROM tblProjectItem AS A"
   mySQL3 += "INNER JOIN tblProjectDevpHistory AS B ON A.ProjectNo = B.ProjectNo AND A.ItemNo = B.ItemNo AND B.DHDate >= '2020/05/01' AND B.DHDate <= '2020/05/31'"
   mySQL3 += "WHERE A.ProjectNo= '" & myDR2("ProjectNo").ToString & "' AND A.ProjectNo < 'S2020'"
   mySQL3 += "GROUP BY A.ItemNo,A.ItemName,A.DepartmentNo,A.PropertyDesc,A.KickOffDate,A.PlanDate,A.EngineerNo,A.AdvisorNo,A.CreatorNo" '(依据2取得的ProjectNo查询Item基本资料) - 笔数不定

6. mySQL = "Insert Into tblProject (ProjectNo, ItemID, ItemName, DepartmentNo, PropertyDesc, KickOffDate, PlanDate, EngineerNo, AdvisorNo, CreatorNo) "
   mySQL += "Values ('" & strNewProjectNo & "','" & myDR("ItemID").ToString & "','" & myDR("ItemName").ToString & "','" & myDR("DepartmentNo").ToString & "','" & myDR("PropertyDesc").ToString & "','" & myDR("KickOffDate").ToString & "','"
            & myDR("PlanDate").ToString & "','" & myDR("EngineerNo").ToString & "','" & myDR("AdvisorNo").ToString & "','" & myDR("CreatorNo").ToString & "') " (1+3+4+5+6须依据2跑回圈执行)

还是有人能将六段SQL整合成一段SQL语法执行?
写到3层While myDR.Read ... End While回圈，总觉得不太对，上​​来问问。

我也会再想想有没有其他写法，也许我会比版上高手快想出来也不一定。

语法可以写成这样吗？查询时又同时执行指令，一层一层的叠上去？

                mySQL="..."
                mySQL1="..."
                mySQL2="..."
                myCnn.Open()
                Dim myCmd As SqlCommand
                myCmd = New SqlCommand(mySQL, myCnn)
                Using myDR As SqlDataReader = myCmd.ExecuteReader
                    While myDR.Read
                        myCnn.Open()
                        myCmd = New SqlCommand(mySQL1, myCnn)
                        Using myDR1 As SqlDataReader = myCmd.ExecuteReader
                            While myDR1.Read
                                .....
                                myCmd = New SqlCommand(mySQL2, myCnn)
                                myCmd.ExecuteNonQuery()

                                myCnn.Open()
                                myCmd = New SqlCommand(mySQL3, myCnn)
                                Using myDR2 As SqlDataReader = myCmd.ExecuteReader
                                    While myDR2.Read
                                        ....
                                    End While
                                End Using
                            End While
                        End Using
                    End While
                End Using

要不是为了整体写法一致，想要都使用SQL语法处理的话，
用阵列和For回圈，基本上直接就能解决了，无难度

一个按钮的功能，大概是这种感觉吧，透过大脑运算后，逻辑上应该差不多是这样写。
只是用上了非SQL方式完成，多声明了好多变量，感觉蛮丑的。希望能征求更简洁的写法。
myCnn一直.Open()没做.Close()不知道会不会有问题？

    '年度转移去年度未完成的Project成今年度新的Project(一年做一次)
    Protected Sub btnAdd1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd1.Click
        If CInt(Session("LevelSD")) > 99 Then
            Dim iYear As String = Format(Now, "yyyy")
            Dim strSerialYearMonth As String = String.Format("{0:yyyyMM}", Now)
            Dim mySQL As String = ""
            Dim mySQL1 As String = "" 'Project List
            Dim mySQL2 As String = "" 'Project
            Dim mySQL3 As String = "" 'Project ITEM
            Dim mySQL4 As String = "" '
            Dim strNewProjectNo As String = ""
            Dim Temp() As String = {""}
            Dim Temp1() As String = {""}
            Dim Temp2() As String = {""}
            Dim i As Integer = 0
            Dim j As Integer = 0
            Dim k As Integer = 0
            Dim strPrefix As String = "S"
            Dim myCmd As SqlCommand
            Try
                '取得ProjectNo (Prefix + YYYYMM + SSS 共10码, eg: S201109001)
                mySQL = "Select MAX(ProjectNo) AS MaxProjectNo From tblProject Where ProjectNo Like '" & strPrefix & strSerialYearMonth & "%'"
                '取得ProjectNo List (2020/05/01 ~ 2020/05/31)
                mySQL1 = "SELECT ROW_NUMBER() OVER (ORDER BY C.CateMainID) AS RowNo,A.ProjectNo FROM tblProjectDevpHistory AS A "
                mySQL1 += "INNER JOIN tblProject AS B ON A.ProjectNo = B.ProjectNo "
                mySQL1 += "INNER JOIN tblBasCategory AS C ON B.CateMainID = C.CateMainID "
                mySQL1 += "INNER JOIN tblProject AS B ON A.ProjectNo = B.ProjectNo "
                mySQL1 += "WHERE A.DHDate >= '2020/05/01' And A.DHDate <= '2020/05/31' And A.ProjectNo < 'S2020' "
                mySQL1 += "GROUP BY A.ProjectNo,C.CateMainID"
                Using myCnn As New SqlConnection(ConfigurationManager.ConnectionStrings(strCnnType & "dbSD").ToString)
                    myCnn.Open()
                    myCmd = New SqlCommand(mySQL, myCnn)
                    Using myDR As SqlDataReader = myCmd.ExecuteReader
                        If myDR.HasRows Then
                            If myDR.Read() Then
                                If Len(myDR("MaxProjectNo").ToString) > 0 Then
                                    strNewProjectNo = strPrefix & strSerialYearMonth & String.Format("{0:000}", CInt(Right(myDR("MaxProjectNo").ToString, 3)) + 1)
                                Else
                                    strNewProjectNo = strPrefix & strSerialYearMonth & "001"
                                End If
                            Else
                                strNewProjectNo = strPrefix & strSerialYearMonth & "001"  '不会发生
                            End If
                        Else
                            strNewProjectNo = strPrefix & strSerialYearMonth & "001"      '不会发生
                        End If
                    End Using
                    myCnn.Open()
                    myCmd = New SqlCommand(mySQL1, myCnn)
                    Using myDR As SqlDataReader = myCmd.ExecuteReader
                        While myDR.Read
                            '储存每个ProjectNo List (2020/05/01 ~ 2020/05/31)
                            ReDim Preserve Temp(j)
                            Temp(j) = myDR("ProjectNo").ToString
                            j += 1
                        End While
                    End Using
                    For i = 0 To UBound(Temp)
                        '取得每个ProjectNo内的资料
                        mySQL2 += "SELECT A.CateMainID,A.CateSubID,A.ProjectName,A.KickOffDate,A.PlanDate,A.DepartmentNo,A.CreatorNo "
                        mySQL2 += "FROM tblProject AS A "
                        mySQL2 += "INNER JOIN tblBasCategory AS B ON A.CateMainID = B.CateMainID "
                        mySQL2 += "INNER JOIN tblProjectItem AS C ON A.ProjectNo = C.ProjectNo "
                        mySQL2 += "INNER JOIN tblProjectDevpHistory AS D ON A.ProjectNo = D.ProjectNo AND C.ItemNo = D.ItemNo AND D.DHDate >= '2020/05/01' AND D.DHDate <= '2020/05/31' "
                        mySQL2 += "WHERE A.ProjectNo = '" & Temp(i).ToString & "' AND A.ProjectNo < 'S2020' "
                        mySQL2 += "GROUP BY A.CateMainID,A.CateSubID,A.ProjectName,A.KickOffDate,A.PlanDate,A.DepartmentNo,A.CreatorNo"
                        strNewProjectNo = strNewProjectNo + i '这部分做字串加减，在SQL上是OK，但是在程式上有疑虑，六月初还需再验证
                        myCnn.Open()
                        myCmd = New SqlCommand(mySQL2, myCnn)
                        Using myDR As SqlDataReader = myCmd.ExecuteReader
                            If myDR.HasRows Then
                                If myDR.Read() Then
                                    '插入新ProjectNo和资料
                                    mySQL = "Insert Into tblProject (ProjectNo, CateMainID, CateSubID, ProjectName, KickOffDate, PlanDate, DepartmentNo, CreatorNo) "
                                    mySQL += "Values ('" & strNewProjectNo & "','" & myDR("CateMainID").ToString & "','" & myDR("CateSubID").ToString & "','" & myDR("ProjectName").ToString & "','" & myDR("KickOffDate").ToString & "','" & myDR("PlanDate").ToString & "','" & myDR("DepartmentNo").ToString & "','" & myDR("CreatorNo").ToString & "') "
                                End If
                            End If
                        End Using
                        '取得每个ProjectNo内每个ITEM和资料
                        mySQL3 += "SELECT A.ItemNo,A.ItemName,A.DepartmentNo,A.PropertyDesc,A.KickOffDate,A.PlanDate,A.EngineerNo,A.AdvisorNo,A.CreatorNo "
                        mySQL3 += "FROM tblProjectItem AS A"
                        mySQL3 += "INNER JOIN tblProjectDevpHistory AS B ON A.ProjectNo = B.ProjectNo AND A.ItemNo = B.ItemNo AND B.DHDate >= '2020/05/01' AND B.DHDate <= '2020/05/31'"
                        mySQL3 += "WHERE A.ProjectNo= '" & Temp(i).ToString & "' AND A.ProjectNo < 'S2020'"
                        mySQL3 += "GROUP BY A.ItemNo,A.ItemName,A.DepartmentNo,A.PropertyDesc,A.KickOffDate,A.PlanDate,A.EngineerNo,A.AdvisorNo,A.CreatorNo"
                        j = 0
                        myCnn.Open()
                        myCmd = New SqlCommand(mySQL3, myCnn)
                        Using myDR As SqlDataReader = myCmd.ExecuteReader
                            While myDR.Read
                                '储存每个ProjectNo内每个ITEM和资料
                                ReDim Preserve Temp1(j)
                                Temp1(j) = Temp(i).ToString & "," & myDR("ItemNo").ToString & "," & myDR("ItemName").ToString & "," & myDR("DepartmentNo").ToString & "," & myDR("PropertyDesc").ToString & "," & _
                                           myDR("KickOffDate").ToString & "," & myDR("PlanDate").ToString & "," & myDR("EngineerNo").ToString & "," & myDR("AdvisorNo").ToString & "," & myDR("CreatorNo").ToString
                                j += 1
                            End While
                        End Using
                        For j = 0 To UBound(Temp1)
                            k = 0 : Temp2 = Split(Temp1(j), ",")
                            If UBound(Temp2) = 9 Then
                                '写入Item 资料表
                                mySQL4 = "Insert Into tblProjectItem (ProjectNo, ItemNo, Weight, ItemName, DepartmentNo, PropertyDesc, KickOffDate, PlanDate, EngineerNo, AdvisorNo, Consultant, CreatorNo) Values ("
                                mySQL4 += "'" & Temp(i).ToString & "'," & (j + 1) & ",100,'" & Temp2(k + 1) & "','" & Temp2(k + 2) & "','" & Temp2(k + 3) & "','" & Temp2(k + 4) & "','" & Temp2(k + 5) & "','" & _
                                     Temp2(k + 6) & "','" & Temp2(k + 7) & "','" & Temp2(k + 8) & "',Null,'" & Temp2(k + 9) & ")"
                                myCmd = New SqlCommand(mySQL4, myCnn)
                                myCmd.ExecuteNonQuery()

                                'Log 专案新增动作
                                mySQL = "Insert Into tblLogEvent (EventTime, EventType, EventUserNo, EventUserName, EventIP, EventDesc, EventMemo, ProjectNo, SerialNo) Values "
                                mySQL += "(GetDate(), 'A', '" & Session("LoginNo") & "', '" & Session("LoginName") & "', '" & Session("LoginIP") & "', '专案主档', "
                                mySQL += "'Project Add', '" & strNewProjectNo & "', Null)"
                                myCmd = New SqlCommand(mySQL, myCnn)
                                myCmd.ExecuteNonQuery()
                            End If
                            Erase Temp2
                        Next j
                        Erase Temp1
                        '发送通知信
                        Call SendEmail_ProjectItemAdd(strNewProjectNo, 1)
                    Next i
                    Erase Temp
                End Using
            Catch ex As Exception
                'XXXXX
            End Try
        Else
            'XXXXX
        End If
    End Sub

[此贴子已经被作者于2020-5-15 12:49编辑过]

上面在Insert Into的时候部分资料有些问题，不过大致上都还好就不再贴了。

strNewProjectNo = strNewProjectNo + i '这部分做字串加减，在SQL上是OK，但是在程式上有疑虑，六月初还需再验证
改为
strNewProjectNo = strPrefix & CStr(CLng(Replace$(strNewProjectNo, strPrefix, "")) + i)

按照存储过程的写法可以实现，不过不建议这样做，你如果只需要最后结果，前面都是为最后做铺垫的，那一定有更好的写法，不用6个select

事后发现执行后有个问题，使用者可能在2019年度的专案下建立了非2019年度的项目，导致这部分的资料搬移后出现异常。
不过因为是少数，所以直接对数据库进行操作，解决了。

不过这原因必须从源头解决，程序内要加卡新增项目的起始时间年分不得与专案起始时间年分不一致。