asked on

I need to SELECT * INTO from one database to another

I use C#, Access and System.Data.OleDb. And I would like to use SELECT * INTO that copies from one database to another.

How do I do that exactly? I can do it easily in the same database. But how, using SQL, do I preface the target table name with the database name, and path?

Thanks,
newbieweb

Sharath S

You can try like this.

select *
into [database1].[dbo].[table1]
from [database2].[dbo].[table2]

rushShah

you can do it like this..

INSERT INTO DB1.dbo.Test
SELECT * FROM [DB2].[dbo].[Test]

HainKurt

use this

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executenonquery(VS.80).aspx

Private Sub CreateOleDbCommand( _
    ByVal queryString As String, ByVal connectionString As String)
    Using connection As New OleDbConnection(connectionString)
        connection.Open()
        Dim command As New OleDbCommand(queryString, connection)
        command.ExecuteNonQuery()
    End Using
End Sub

CreateOleDbCommand("INSERT INTO DB1.dbo.Test SELECT * FROM DB2.dbo.Test", myConnectionString)
or
CreateOleDbCommand("select * into [database1].[dbo].[table1] from [database2].[dbo].[table2]", myConnectionString)

Open in new window

curiouswebster

ASKER

I do not get how to connect "DB1" or "Database1" with

c:\tmp\MyDatabase1.mdb

Please give me an example.

And I use C#.

HainKurt

try this

INSERT INTO DB1.dbo.Test SELECT * FROM DB2.dbo.Test in 'c:\tmp\MyDatabase1.mdb'

ASKER CERTIFIED SOLUTION

Venkatgvi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

curiouswebster

ASKER

true. Good idea.

curiouswebster

ASKER

I think I was overzealous. I have a password on the main database, where I would need to link into, from the database with no pasword. It was too early to close the question. Sorry about that.