# Database Discussions > Microsoft Access >  SQL query to copy table

## Erick

Please help me out !

I would appreciate if anybody could tell me if it is possible to fully 
copy a table using a SQL query and using no stored procedure (sp_). I tryed "SELECT INTO" but 
it does not copy the columns properties like default 
values.

Thanks in advance,
Regards,
Erick

----------


## Rich Zore

I've used this routine in the click event for a button on a VB6 form.  I presume that something similar would work for Access.  The routine scans the contents of a combo box which holds table names and then copies each of these tables from a live database to a test database.  In the code to_rs refers to a recordset in the test database and from_rs referes to a recordset in the live db.  Good luck.

Rich

Private Sub cmdCopyLive2Test_Click()
'   NOTE - It is up to the IT staff to insure that the structure of the live and test TimeCard
'       databases remains in sync.  If they aren't in sync as far as structure is concerned, this
'       procedure cannot help but fail at some level.
    On Error GoTo Error_Handler
    Dim i1, i2 As Integer
    Dim from_rs, to_rs As adodb.Recordset
    Dim SQLcmd As String
    If Not glo.LiveDB Then
        Exit Sub
    End If
'       Connect to test database
    Screen.MousePointer = vbHourglass

    Dim Test_DB As New adodb.Connection
    Test_DB.ConnectionString = _
        "Driver={mySQL};" & _
        "Server=XXXXX.XXXXX.XXXXX;" & _
        "Port=3306;" & _
        "Option=131072;" & _
        "DSN=TimeCardsTest;" & _
        "Database=TimeCardsTest;" & _
        "Uid=tcu;" & _
        "Pwd=XXXXX"
    Test_DB.Open
    Set to_rs = New adodb.Recordset
    Set from_rs = New adodb.Recordset
    For i1 = 0 To cmbTables.ListCount - 1
'       Clear to test table
        cmbTables.ListIndex = i1
        SQLcmd = "Select * from " & LCase(cmbTables)
        If to_rs.State = adStateOpen Then
            to_rs.Close
        End If
        to_rs.CursorType = adOpenDynamic
        to_rs.CursorLocation = adUseServer
        to_rs.LockType = adLockPessimistic
        to_rs.Open SQLcmd, Test_DB
        While Not to_rs.EOF And Not to_rs.BOF
            to_rs.Delete
            to_rs.MoveNext
        Wend
        If from_rs.State = adStateOpen Then
            from_rs.Close
        End If
        from_rs.CursorType = adOpenForwardOnly
        from_rs.CursorLocation = adUseServer
        from_rs.LockType = adLockOptimistic
        from_rs.Open SQLcmd, glo.DB
        While Not from_rs.EOF And Not from_rs.BOF
'          Populate test table with Live data
            to_rs.AddNew
            For i2 = 0 To from_rs.Fields.Count - 1
                to_rs.Fields(i2).Value = from_rs.Fields(i2).Value
            Next
            to_rs.Update
            from_rs.MoveNext
        Wend
    Next
    If from_rs.State = adStateOpen Then
        from_rs.Close
    End If
    If to_rs.State = adStateOpen Then
        to_rs.Close
    End If
    Test_DB.Close
    Set from_rs = Nothing
    Set to_rs = Nothing
    Set Test_DB = Nothing
    Screen.MousePointer = vbDefault
    Exit Sub
Error_Handler:
    MsgBox (Err.Number & " - " & Err.Description & " in " & Err.Source)
End Sub

----------


## PinkPanther2003

Seems like alot of effort, and it's the table definition, not the data thats not being imported across by the SELECT INTO.

SELECT INTO doesn't copy across table definitions.

I presume you are moving a table from MS Access (version??) to MS SQL Server (version??).

If you are using SQL server it won't create the contraints, indexes etc.

Write the T SQL to do it yourself and save it in case you need to run it again.

Just my opinion,

Peter

----------

