copy table from sqlserver1 to sqlserver2

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

If you are only doing this once it is best to load SQL manager onto your PC and do it from there.

cheers, Paul

Reply | Quote

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

Reply | Quote