事の発端は、DB1からDB2へデータをまるっとコピーしようと思ったんだけど、
どうもINDEXまではコピーしてくれない模様、、 だから全フィールドをなめてSQLを作る関数を作ってみた。
Private Function getCnvSQL(cn As ADODB.Connection, old_dbname$, new_dbname$, tblname$) Dim old_name$: old_name$ = old_dbname$ & ".dbo." & tblname$ Dim new_name$: new_name$ = new_dbname$ & ".dbo." & tblname$ Dim sql1$: sql1 = "truncate table " & new_name & ";" Dim sql2$: sql2 = "SET IDENTITY_INSERT " & new_name & " ON;" Const SQLFLD_base$ = "SELECT name FROM [dbname].sys.Columns" & _ " WHERE object_id = (SELECT object_id FROM [dbname].sys.Tables" & _ " WHERE name = '[tblname]')" Dim sql$ sql$ = Replace(SQLFLD_base$, "[dbname]", old_dbname$) sql$ = Replace(sql$, "[tblname]", tblname$) Dim flds$: flds$ = "" Dim swFirst As Boolean: swFirst = True Dim rs As ADODB.Recordset Set rs = GFC_GetRs(cn, False, sql$) Do While Not rs.EOF If rs!Name <> "SSMA_TimeStamp" Then If swFirst Then flds$ = flds$ & rs!Name swFirst = False Else flds$ = flds$ & "," & rs!Name End If End If rs.MoveNext Loop rs.Close Dim sql3$: sql3$ = "insert " & new_name & "(" & flds$ & ") SELECT " & flds$ & " from " & old_name$ & ";" Dim sql4$: sql4 = "SET IDENTITY_INSERT " & new_name & " OFF;" getCnvSQL = sql1$ & sql2$ & sql3$ & sql4$ End Function
あれ、はてブロって、ソースの行番号でないのかな、、