Copy Table to SQL Server using VB

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    I've pasted some code that a friend gave me for copying a table from an SQL server to a database held on a local pc, my question is, is there a way of doing this in reverse, i.e from a locally held database to the sql server?

     

     

    Function Copy_Table_From_Sql_Server(ByVal NewTable As String, ByVal ExistingTable As String, ByVal LocalDb As Database) As Boolean

    On Error GoTo ErrHandler

    CopyTable:  'try and copy before deleting the existing table

                'just incase it doesn't exist in the external db

               

        Sql = "SELECT * INTO [" & NewTable & "] FROM " & _

            "[ODBC;Driver=SQL Server; SERVER=10.234.95.121;DATABASE=Titan;UID=UserName;PWD=Password;].[" & ExistingTable & "];"

        LocalDb.Execute Sql

               

        Copy_Table_From_Sql_Server = True

    Exit Function

       

    DeleteTable:

        LocalDb.Execute "drop table [" & NewTable & "] ;" ' drop the old table

        GoTo CopyTable

    ErrHandler:

        Select Case Err

            Case 3376

                Resume Next ' table didn't exist to begin with

            Case 3010

                Resume DeleteTable ' table allready existed

            Case 3078

                Exit Function ' table doesn't exist in external db

            Case Else

           

    '            MsgBox Err.Number & " " & Err.Description

        End Select

    End Function

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Can't you just link the tables?

    Can't you also just copy that code on that db on do the reverse process... or code and insert into select...

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    as far as I know it doesn't work in reverse, I have tried it without any success

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    You can't sue select into to get the data back, but you can do insert into select

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    can you explain that to me? sorry but I'm very new to this

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Insert into tableName (col1, col2, col3) Select col1, col2, col3 from OtherTable. Looks like you can do something like that using the code you just presented.

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    ok thanks, I'll give that a try

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    no good, I've tried, is there anyone who can help me out with some code?

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    What did you try?

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    the code I pasted above but in reverse

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Please POST IT.

  • sushila

    SSC-Dedicated

    Points: 35293

    Here's an alternate suggestion - why don't you script the "create table"...export the data to a file (word, excel..etc.) - run the script on your target database and import the data from the file?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Function Copy_Table_From_Sql_Server(ByVal NewTable As String, ByVal ExistingTable As String, ByVal LocalDb As Database) As Boolean

    On Error GoTo ErrHandler

    CopyTable:  'try and copy before deleting the existing table

                'just incase it doesn't exist in the external db

               

        Sql = "SELECT * INTO "[ODBC;Driver=SQL Server; SERVER=10.234.95.121;DATABASE=Titan;UID=UserName;PWD=Password;].[" & ExistingTable & "];"

    from Newtable

        LocalDb.Execute Sql

               

     

     

     

     

     

        Copy_Table_From_Sql_Server = True

    Exit Function

       

    DeleteTable:

        LocalDb.Execute "drop table [" & NewTable & "] ;" ' drop the old table

        GoTo CopyTable

    ErrHandler:

        Select Case Err

            Case 3376

                Resume Next ' table didn't exist to begin with

            Case 3010

                Resume DeleteTable ' table allready existed

            Case 3078

                Exit Function ' table doesn't exist in external db

            Case Else

           

    '            MsgBox Err.Number & " " & Err.Description

        End Select

    End Function

     

    ********************************************************************************

    That's the nearest I've got, but as you can see it was a non starter

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    The insert statement MUST look like this :

    Insert into SomeTable (col1, col2...)

    Select col1, col2... from YourOuterDataSource

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply