VB & Database, Inserting data from an ADO recordset into a SQL Server table

Inserting data from an ADO recordset into a SQL Server table

Author	Message
magictec #1 / 7	Inserting data from an ADO recordset into a SQL Server table I'm trying to insert data from an ADO recordset into SQL Server table. I understand there are other ways to insert data into tables, i.e. DTS, BCP and T-SQL. However, using ADO happens to be the most suitable approach in my situation. Here is the code that I'm having problems with. Does anyone know how to insert data into a SQL Server table from an ADO recordset? Please any help would be greatly appreciated. Thanks in advance '**************************** Do While Not oRs.EOF Insert molap.dbo.holap oRs.MoveNext Loop '***************************** -- Direct access to this group with http://www.--.com/ http://www.--.com/
Fri, 09 Dec 2005 09:55:05 GMT

Val Mazu #2 / 7	Inserting data from an ADO recordset into a SQL Server table Hi, Did you open recordset from one database and trying to insert those retrieved records into another one? If yes, then there are several ways to do that 1. Inside loop you would need to build INSERT SQL statement with values from each record and execute that statement. It is pretty slow way, because you will execute number of INSERT statements, which is equal to number of records in a recordset 2. Use XML to transform your recordset to another ADO recordset, which will mark all records inside that recordset as inserted. In that case you could transfer records from the recordset in one shot using UpdateBatch method. That method could be slow in case if recordset is very big. If you need example, then send me e-mail and I will send it to you. It is too big to post it here 3. Use DTS. I think this is most powerful way to transfer data between different databases -- Val Mazur Microsoft MVP Quote: > I'm trying to insert data from an ADO recordset into SQL Server table. I > understand there are other ways to insert data into tables, i.e. DTS, > BCP and T-SQL. However, using ADO happens to be the most suitable > approach in my situation. > Here is the code that I'm having problems with. Does anyone know how to > insert data into a SQL Server table from an ADO recordset? Please any > help would be greatly appreciated. Thanks in advance > '**************************** > Do While Not oRs.EOF > Insert molap.dbo.holap > oRs.MoveNext > Loop > '***************************** > -- > Direct access to this group with http://web2news.com > http://web2news.com/?microsoft.public.vb.database.ado
Fri, 09 Dec 2005 11:14:21 GMT

magictec #3 / 7	Inserting data from an ADO recordset into a SQL Server table Quote: > Hi, > Did you open recordset from one database and trying to insert those > retrieved records into another one? > If yes, then there are several ways to do that > 1. Inside loop you would need to build INSERT SQL > statement with values from > each record and execute that statement. It is pretty slow > way, because you > will execute number of INSERT statements, which is equal to number of > records in a recordset > 2. Use XML to transform your recordset to another ADO > recordset, which will > mark all records inside that recordset as inserted. In > that case you could > transfer records from the recordset in one shot using > UpdateBatch method. > That method could be slow in case if recordset is very > big. If you need > example, then send me e-mail and I will send it to you. It > is too big to > post it here > 3. Use DTS. I think this is most powerful way to transfer > data between > different databases Hi , I prefer the first option.Here is a sample of my code. However, it doesn't seem to be working. Could you point out what is it that I'm doing wrong. Thanks in advance. ****************************** Do While Not oRs.EOF Insert molap.dbo.holap select * from recordset-name oRs.MoveNext Loop '******************************* Could Regards magictech -- Direct access to this group with http://web2news.com http://web2news.com/?microsoft.public.vb.database.ado
Fri, 09 Dec 2005 20:10:32 GMT

Val Mazu #4 / 7	Inserting data from an ADO recordset into a SQL Server table Hi, You cannot use recordset directly inside SQL statement. You need to build it dynamically. You could do that using Command ADO object to prepare INSERT statement and pass values to insert taking them from recordset -- Val Mazur Microsoft MVP Quote: > > Hi, > > Did you open recordset from one database and trying to insert those > > retrieved records into another one? > > If yes, then there are several ways to do that > > 1. Inside loop you would need to build INSERT SQL > > statement with values from > > each record and execute that statement. It is pretty slow > > way, because you > > will execute number of INSERT statements, which is equal to number of > > records in a recordset > > 2. Use XML to transform your recordset to another ADO > > recordset, which will > > mark all records inside that recordset as inserted. In > > that case you could > > transfer records from the recordset in one shot using > > UpdateBatch method. > > That method could be slow in case if recordset is very > > big. If you need > > example, then send me e-mail and I will send it to you. It > > is too big to > > post it here > > 3. Use DTS. I think this is most powerful way to transfer > > data between > > different databases > Hi , > I prefer the first option.Here is a sample of my code. However, it > doesn't seem to be working. Could you point out what is it that I'm > doing wrong. Thanks in advance. > ****************************** > Do While Not oRs.EOF > Insert molap.dbo.holap > select * from recordset-name > oRs.MoveNext > Loop > '******************************* > Could > Regards > magictech > -- > Direct access to this group with http://web2news.com > http://web2news.com/?microsoft.public.vb.database.ado
Fri, 09 Dec 2005 20:32:05 GMT

magictec #5 / 7	Inserting data from an ADO recordset into a SQL Server table Quote: > Hi, > You cannot use recordset directly inside SQL statement. > You need to build it > dynamically. You could do that using Command ADO object to > prepare INSERT > statement and pass values to insert taking them from recordset Hi Val, I truely appreciate you helping me out on this. Do you happend to have any example on how to use "Command ADO object to Quote: > prepare INSERT statement and pass values to insert taking them from recordset"? Please I realy need help. Thanks is advance. Regards -- Direct access to this group with http://web2news.com http://web2news.com/?microsoft.public.vb.database.ado
Fri, 09 Dec 2005 21:28:29 GMT

Val Mazu #6 / 7	Inserting data from an ADO recordset into a SQL Server table Hi, It could be something like Dim loCommand As ADODB.Command Set loCommand = New ADODB.Command Set loCommand.ActiveConnection = loConnection lcSQL = "INSERT INTO MyTable (Field1, Field2) VALUES (?,?)" loCommand.CommandText = lcSQL loCommand.CommandType = adCmdText adInteger, adParamInput)) adVarChar, adParamInput, 4) Do While Not oRs.EOF loCommand.Parameters(1).Value=oRS.Fields("SomeField1").Value loCommand.Parameters(2).Value=oRS.Fields("SomeField2").Value loCommand.Execute oRs.MoveNext Loop -- Val Mazur Microsoft MVP Quote: > > Hi, > > You cannot use recordset directly inside SQL statement. > > You need to build it > > dynamically. You could do that using Command ADO object to > > prepare INSERT > > statement and pass values to insert taking them from recordset > Hi Val, > I truely appreciate you helping me out on this. Do you happend to have > any example on how to use "Command ADO object to > > prepare INSERT statement and pass values to insert taking them from > recordset"? > Please I realy need help. Thanks is advance. > Regards > -- > Direct access to this group with http://web2news.com > http://web2news.com/?microsoft.public.vb.database.ado
Fri, 09 Dec 2005 23:59:48 GMT

Richard Fo #7 / 7	Inserting data from an ADO recordset into a SQL Server table My experience is small, as I am just tackling the same task as you, but I believe you have to use the Recordset.Update() function to affect the database. There are lots of examples and different ways to do this, see the O'Reilly book "ADO" Rich
Sun, 11 Dec 2005 05:09:31 GMT

Page 1 of 1

[ 7 post ]