copy tables between databases

[soapdad]

Does anyone have any suggestions on the best way to copy tables between two MSSQL databases? My ISP does not allow scheduled DTS jobs so that is not an option. I am creating an ASP application which will combine data from satellite tables on different databases into one master table. I can combine them when I get them into the master DB, but getting the tables there seems to be a problem.
Any thoughts will be greatly appreciated!
Thanks

 

[gmmastros]

Something like....

Insert Into [DatabaseName].[OwnerName].[TableName] (Fieldlist....)
Select FieldList... From [SecondDatabaseName].[OwnerName].[TableName]

 

[soapdad]

I guess I would have to establish two connections and then go this route?
Thanks!

 

[gmmastros]

No, you shouldn't need 2 connections. I've done this several times, each time it worked like a charm.

A coupla things to be careful of. The insert into method requires that the table already exists in the destination database.

If you post some specific, I may be able to help a little better.

 

[miq]

HI,

coupla things to be careful of. The insert into method requires that the table already exists in the destination database.

Not exactly - to be precise. If destination table is not present select into will create one for you provided you have object creation permission in destination DB (Sa, System Admin, DDL Admin for instance). But constrains (if any)will not be copied to the destination table you have to recreate them .

B.R,
miq

 

[soapdad]

I'm trying not to sound too dumb here, as I usually do very simplistic SQL stuff, so...

Anyway, I normally open a connection to the database with a line like this:
Connection.Open "DRIVER={SQL Server}; Server=" & varServerIP & "; Database=" & varDataBaseName & "; UID=" & varUserName & "; PWD=" & varPassword'

Then I normally setup the query and run like this:
filter = "Select * from filename"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open filter, Connection, ,adLockOptimistic

As the table I want to copy from is in database "A" and I want to copy it to database "B", wouldn't I have to establish a connection with both databases to establish my identity and rights with each server?

I think I've got the query syntax, but I don't understand how I can run a query without making the connection first.

Once again, I apologize if this sounds dumb.
Thanks.....

 

[hmckillop]

As this is a SQL forum, most people probably thought you wanted a SQL answer.
In your case if you login to both databases with the same username and password and they both reside on the same server, you could use a statement like shown by gmmastros.
Otherwise you would require a seperate approach like setting up a trusted database connection, or taking all the data back to the client side opening up a new connection to the second database and executing it row by row (obviously not very effiecient and possibly prone to errors).

I would suggest if this is a one off exercise to complete it using SQL Query Analyser and not through code.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[miq]

HI,
what you need to do here is open connection, open recordset, populate recordset, disconnect recordest (i quess you know how to do it), open new connection, attach recordset to that connection, and writeback recorset.


B.R,
miq

 

[gmmastros]

Wow, reading my second post here, you'd think I was drunk. Nope, just tired.

miq makes a good point about the destination table not existing in the destination database. There are actually 2 ways you can copy your data. The method you choose depends upon the existence of the table in the destination database.

If the table does NOT exist....
Select Field1, Field2, ...
Into DestinationDatabase.dbo.TableName
From SourceDatabase.dbo.TableName

If the table ALREADY exists in the destination database....
Insert Into DestinationDatabase.dbo.TableName(Field1, Field2, ...)
Select Field1, Field2, ... From SourceDatabase.dbo.TableName

There really are a couple of things to worry about. First, you must have appropriate permissions in both databases. Second, Bulk Copy/Insert Into must be enabled in the destination database. And third, you must know whether the destination database already contains the table (as I already explained).

The recordset object is used to return data back from the database. Since neither SQL will return data, you don't need to use the recordset object. After creating your connection object, simply call the execute method with the SQL in it.

Ex.

strSQL = "Select Field1, field2, ....
Connetion.Execute(strSQL)

 

[miq]

HI,
if both DBs reside on the same server the ideal string would be:

connstr = "provider=sqloledb;data source=server_name;uid=varusername;pwd=varpassword"

connection.open connstr

prepare select/insert statement here
......

connection.execute (sqlstatement)

Here you are not defining initial database name just connection as a whole to sqlserver so, you can access any database for which you have permissions hence, need just single connection, the only requirement is to use 3part name for table references in select /insert statement.

B.R,
miq