|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Inserting from one db into anotherHi
I have open connection to two separate databases. I now need to insert records from a table in one db into a table in second db. How can I go about doing it? Thanks Regards Read from one, write to the other.
Robin S. ----------------------------------------------- Show quote "John" <John@nospam.infovis.co.uk> wrote in message news:ugfmMrfNHHA.5012@TK2MSFTNGP02.phx.gbl... > Hi > > I have open connection to two separate databases. I now need to insert > records from a table in one db into a table in second db. How can I go > about doing it? > > Thanks > > Regards > > "RobinS" wrote: of course RobinS!> Read from one, write to the other. > '// first it's source database you want to read. SqlCon.Open() '// you already get the data and fill to dataset SqlDataAdapter.fill(Dataset1) '// Then Change database destination to database you want to write. SqlCon.ChangeDatabase("DestinationDatabase") '// Insert Now. SqlDataAdapter.InsertCommand.CommandText = SQL Query + (Dataset value) SqlDataAdapter.InsertCommand.ExecuteNonQuery It's really works ;) Denny Lim,
If the table in the Source Database field names are FieldA and FieldB and the "DestinationDatabase" has fields are Field1 and Field2, what would the SQL Query + (Dataset value) be in your example below? Jim Denny Lim wrote: Show quote > "RobinS" wrote: > >> Read from one, write to the other. >> > > of course RobinS! > > '// first it's source database you want to read. > SqlCon.Open() > > '// you already get the data and fill to dataset > SqlDataAdapter.fill(Dataset1) > > '// Then Change database destination to database you want to write. > SqlCon.ChangeDatabase("DestinationDatabase") > > '// Insert Now. > SqlDataAdapter.InsertCommand.CommandText = SQL Query + (Dataset value) > SqlDataAdapter.InsertCommand.ExecuteNonQuery > > It's really works ;) > John wrote: > Hi > > I have open connection to two separate databases. I now need to insert > records from a table in one db into a table in second db. How can I go about > doing it? > > Thanks > > Regards Hi John?
no need use two connection :) the simple : SqlCon.Open() SqlDataAdapter.fill(Dataset1) SqlCon.ChangeDatabase("DestinationDatabase") SqlDataAdapter.InsertCommand.CommandText = SQL Query + (Dataset value) SqlDataAdapter.InsertCommand.ExecuteNonQuery actually im not use code like that. ( it's simple for you to understands) Show quote :) Thanks.
Regards Show quote "Denny Lim" <Denny***@discussions.microsoft.com> wrote in message news:8F2C6E3B-A0BB-45A3-A7BE-F6B5BEB0319D@microsoft.com... > Hi John? > > no need use two connection :) > > the simple : > SqlCon.Open() > SqlDataAdapter.fill(Dataset1) > SqlCon.ChangeDatabase("DestinationDatabase") > SqlDataAdapter.InsertCommand.CommandText = SQL Query + (Dataset value) > SqlDataAdapter.InsertCommand.ExecuteNonQuery > > actually im not use code like that. ( it's simple for you to understands) > :) > Will something like below also work;
LocalConn.Open() Dim DBCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO Orders SELECT * FROM [Orders] IN ''[ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes];'', LocalConn) DBCommand.ExecuteNonQuery() LocalConn.Close() Thanks Regards Show quote "Denny Lim" <Denny***@discussions.microsoft.com> wrote in message news:8F2C6E3B-A0BB-45A3-A7BE-F6B5BEB0319D@microsoft.com... > Hi John? > > no need use two connection :) > > the simple : > SqlCon.Open() > SqlDataAdapter.fill(Dataset1) > SqlCon.ChangeDatabase("DestinationDatabase") > SqlDataAdapter.InsertCommand.CommandText = SQL Query + (Dataset value) > SqlDataAdapter.InsertCommand.ExecuteNonQuery > > actually im not use code like that. ( it's simple for you to understands) > :) > On Fri, 12 Jan 2007 11:54:42 -0000, "John" <John@nospam.infovis.co.uk> wrote: ¤ Will something like below also work;¤ ¤ LocalConn.Open() ¤ ¤ Dim DBCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO Orders ¤ SELECT * FROM [Orders] IN ''[ODBC;Driver={SQL ¤ Server};Server=(local);Database=Northwind;Trusted_Connection=yes];'', ¤ LocalConn) ¤ ¤ DBCommand.ExecuteNonQuery() ¤ ¤ LocalConn.Close() Yes it will work as long as the columns map properly in each table. Otherwise, the column names may need to be specified in the SQL statement. Paul ~~~~ Microsoft MVP (Visual Basic) Dim DBCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO Orders
> SELECT * FROM [Orders] IN ''[ODBC;Driver={SQL it's really works, but ...> Server};Server=(local);Database=Northwind;Trusted_Connection=yes];'', > LocalConn) assume you got a lot insert, update and delete method like above in multiple Classes. each query need specify 'Server' and other properties connection. In fact you want to move connection to another server. Server=(local) ??? you need change all you code! ( OOP = is useless) Code readability is needed. hope this can help too ;) If the target database is SQL Server you do NOT want to simply perform
INSERT statements unless you're being paid by the length of time it takes to run your program. ADO and all of the other data access interfaces are not designed to move data from server-to-server. Use DTS, BCP or better yet ADO.NET 2.0's SqlBulkCopy API. hth -- Show quote____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP INETA Speaker www.betav.com/blog/billva www.betav.com Please reply only to the newsgroup so that others can benefit. This posting is provided "AS IS" with no warranties, and confers no rights. __________________________________ Visit www.hitchhikerguides.net to get more information on my latest book: Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ----------------------------------------------------------------------------------------------------------------------- "John" <John@nospam.infovis.co.uk> wrote in message news:ugfmMrfNHHA.5012@TK2MSFTNGP02.phx.gbl... > Hi > > I have open connection to two separate databases. I now need to insert > records from a table in one db into a table in second db. How can I go > about doing it? > > Thanks > > Regards > > Hi Bill
It is just a few records 10-20 at a time. I just need an easy way to run insert and update queries between the two dbs. Thanks Regards Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message news:OyBIjTnNHHA.4604@TK2MSFTNGP06.phx.gbl... > If the target database is SQL Server you do NOT want to simply perform > INSERT statements unless you're being paid by the length of time it takes > to run your program. > > ADO and all of the other data access interfaces are not designed to move > data from server-to-server. Use DTS, BCP or better yet ADO.NET 2.0's > SqlBulkCopy API. > > hth > > -- > ____________________________________ > William (Bill) Vaughn > Author, Mentor, Consultant > Microsoft MVP > INETA Speaker > www.betav.com/blog/billva > www.betav.com > Please reply only to the newsgroup so that others can benefit. > This posting is provided "AS IS" with no warranties, and confers no > rights. > __________________________________ > Visit www.hitchhikerguides.net to get more information on my latest book: > Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) > and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) > ----------------------------------------------------------------------------------------------------------------------- > > "John" <John@nospam.infovis.co.uk> wrote in message > news:ugfmMrfNHHA.5012@TK2MSFTNGP02.phx.gbl... >> Hi >> >> I have open connection to two separate databases. I now need to insert >> records from a table in one db into a table in second db. How can I go >> about doing it? >> >> Thanks >> >> Regards >> >> > > Well William ...
> ADO and all of the other data access interfaces are not designed to move yes you're right!> data from server-to-server. Use DTS, BCP or better yet ADO.NET 2.0's > SqlBulkCopy API. but remember! with feature you said above need more than lot process and multiple click :) with Executeable Program at least move that record just with 3 mouse clicks :)  |
|||||||||||||||||||||||
Other interesting topics