7,763
社区成员
发帖
与我相关
我的任务
分享
Option Explicit
Sub Main()
Dim cn As ADODB.Connection
Dim rsTable As ADODB.Recordset
Dim rsColumns As ADODB.Recordset
Dim fld As ADODB.Field
Dim sList As String
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;" & _
"Data Source=(local);Initial Catalog=db1;" & _
"User ID=sa;password=;"
Set rsTable = cn.Execute("sp_tables @table_type = ""'TABLE'""")
While Not rsTable.EOF
sList = vbNullString
Set rsColumns = cn.Execute("sp_columns @table_name = '" & rsTable!TABLE_NAME & "'")
While Not rsColumns.EOF
sList = sList & "," & rsColumns!COLUMN_NAME
rsColumns.MoveNext
Wend
sList = Mid$(sList, 2)
Debug.Print "INSERT INTO db2.dbo." & rsTable!TABLE_NAME;
Debug.Print "(" & sList & ")";
Debug.Print " SELECT " & sList;
Debug.Print " FROM db1.dbo." & rsTable!TABLE_NAME
rsTable.MoveNext
Wend
End Sub