Avatar of curiouswebster
curiouswebster
Flag for United States of America 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
.NET ProgrammingMicrosoft Access

Avatar of undefined
Last Comment
curiouswebster

8/22/2022 - Mon
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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
curiouswebster

ASKER
true. Good idea.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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.