Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How to via vb6 classic or vba for excel...
    Copy table1 from database1 in sqlserver1 to database2 in sqlserver2?
    note:
    I have user and password trusted in windows login.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    That is a "select *" command to read the data into a variable / array / dictionary, then either a "drop" and "create", or "update / set" command to populate the second table.
    Is the second table empty, does it have the same number of entries as the first, do you need to do this often? As much info as you can please?

    cheers, Paul

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Do you need to copy the table or just the data? ie does the table already exist in the second database?

    To copy the data you can use two ADO connections.

    Moving through the recordset in the first database and appending it to the second.

    To create the table itself, if you need to, then do you want to do this in code as well or manually.

    Manually - in Management Studio, right click on the table, select Script Table As > Create To > New Query Editor WIndow.

    This will create a query for you to run on the second database to create the same table structure.

    Or if you want to create the table in the second database using code then you can use the query you just created to build DDL code that you can run against your ADO connection.

    So to answer your question, we really need to know more about what you need to do and how much you already know

    eg how much do you know about using ADO recordsets?

    Can you create the tables in Management Studio? If not then are you familiar with DDL?

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kentg View Post
    Do you need to copy the table or just the data? ie does the table already exist in the second database?

    - table and the data

    To copy the data you can use two ADO connections.

    Moving through the recordset in the first database and appending it to the second.

    To create the table itself, if you need to, then do you want to do this in code as well or manually.

    Manually - in Management Studio, right click on the table, select Script Table As > Create To > New Query Editor WIndow.

    This will create a query for you to run on the second database to create the same table structure.

    Or if you want to create the table in the second database using code then you can use the query you just created to build DDL code that you can run against your ADO connection.

    So to answer your question, we really need to know more about what you need to do and how much you already know

    eg how much do you know about using ADO recordsets?

    Can you create the tables in Management Studio? If not then are you familiar with DDL?
    - no
    - no

  5. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    If you are only doing this once it is best to load SQL manager onto your PC and do it from there.

    cheers, Paul

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    You are going to have to supply a bit more detail to get an answer.

    Like if you can access Studio Manager and if not why not. Can you access the first Databse via SM and not the second etc.

    I can provide you with some code like the following but unless we know more we can't give you any real solution.

    Code:
    Dim cn As ADODB.Connection
    Dim strSQL As String
    
        Set cn = New ADODB.Connection
        cn.Open "dsn=Database2"
        
        strSQL = "CREATE TABLE LocalSetting ( SettingName VarChar(50) CONSTRAINT SettingName_pk PRIMARY KEY, SettingValue varchar(100))"
        cn.Execute strSQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •