Inserting data from an ADO recordset into a SQL Server table 
Author Message
 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  
 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  
 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  
 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  
 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  
 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  
 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  
 
 [ 7 post ] 

 Relevant Pages 

1. Inserting a recordset data into a table in SQL Server

2. Insert Data Into Access Database From SQL Server ADO Insert

3. Mass Insert into SQL 6.5 from data in a RecordSet ADO object

4. Inserting data from SQL Server into Access97 table

5. ADO, SQL Server Updating Recordset using 2 Tables

6. recordset data -> temporary table (sql-server)

7. Inserting data into mutliple tables using SQL Insert Command

8. INSERT INTO from SQL Server table into Access 97 table

9. VB6 - CR7 - SQL Server - Want to send either SQL or ADO recordset to report

10. ADO, SQL Server, VB problem posted to the microsoft.public.data.ado group as well

11. ADO: Inserting - Via a Recordset or Direct SQL

12. Inserting records from one database table to another in SQL Server

 

 
Powered by phpBB® Forum Software