Joining two recordsets 
Author Message
 Joining two recordsets

I have two recordsets both with exactly the same structure. I want to add
them together. Is there a simple way to do this?
Thanks,
Roy


Tue, 03 Feb 2004 21:08:37 GMT  
 Joining two recordsets

Quote:

> I have two recordsets both with exactly the same structure. I want to add
> them together. Is there a simple way to do this?

There isn't a clean way to do this with ADO.  You'll have to create a third
disconnected recordset and manually add the fields & records from teh first
two to get the merge. You can also use intermediate helpers like XML etc.,
but look at the perf. requirements also.

 -- Dev



Tue, 03 Feb 2004 22:03:08 GMT  
 Joining two recordsets
If you want to keep the recordsets (tables?) seperate,
you might want to make a query to add them together.
This way the tables themselves stay as they were.
To do this: just create a SQL query like this:

SELECT * from table1
UNION ALL select * from table2;

Where 'table1' and 'table2' of course are the names of
the tables you want to join together.
This works with fine with queries as well.

good luck!


Quote:
> I have two recordsets both with exactly the same structure. I want to add
> them together. Is there a simple way to do this?
> Thanks,
> Roy



Wed, 04 Feb 2004 19:07:47 GMT  
 Joining two recordsets
Sorry guys I didn't explain myself very well.
First of all it's DAO. What I have is a recordset
that has been returned to me from an object
and I want to add it to a table. At the moment
my code gets access to the table by running a
db.openrecordset and then loops through the
recordset from the object adding one row at a time.
I was wondering if there was an easier way to do
it as this seemed a bit long winded.
Many thanks for your help,
Roy


Quote:
> If you want to keep the recordsets (tables?) seperate,
> you might want to make a query to add them together.
> This way the tables themselves stay as they were.
> To do this: just create a SQL query like this:

> SELECT * from table1
> UNION ALL select * from table2;

> Where 'table1' and 'table2' of course are the names of
> the tables you want to join together.
> This works with fine with queries as well.

> good luck!



Thu, 05 Feb 2004 01:06:24 GMT  
 Joining two recordsets


Quote:
> Sorry guys I didn't explain myself very well.
> First of all it's DAO. What I have is a recordset
> that has been returned to me from an object
> and I want to add it to a table. At the moment
> my code gets access to the table by running a
> db.openrecordset and then loops through the
> recordset from the object adding one row at a time.
> I was wondering if there was an easier way to do
> it as this seemed a bit long winded.

No there isn't, not if you insist on using the returned recordset.

However ...

What you could do is use the SQL statement contained in the returned
Recordset's Name property to build an Insert query (as long as the data
source for the recordset is in the same database as the table you wish to
populate). Like this:

Dim sSQL As String
Dim rs As Recordset
Set rs = FunctionThatReturnsRecordset(<parameters>)
If rs.RecordCount > 0 then
    sSQL = "Insert Into <tablename> " & rs.Name
    db.execute sSQL, dbFailOnError
End If

HTH,
Bob Barrows



Thu, 05 Feb 2004 01:43:16 GMT  
 Joining two recordsets

Quote:

> No there isn't, not if you insist on using the returned recordset.

> However ...

> What you could do is use the SQL statement contained in the returned
> Recordset's Name property to build an Insert query (as long as the data
> source for the recordset is in the same database as the table you wish to
> populate). Like this:

> Dim sSQL As String
> Dim rs As Recordset
> Set rs = FunctionThatReturnsRecordset(<parameters>)
> If rs.RecordCount > 0 then
>     sSQL = "Insert Into <tablename> " & rs.Name
>     db.execute sSQL, dbFailOnError
> End If

> HTH,
> Bob Barrows

Bob,
Oooo - fascinating - I didn't know that was there!!! The SQL that is...
That opens up all sorts of possibilities.
Many thanks for your response.
Roy


Thu, 05 Feb 2004 03:58:13 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Join two recordsets into one using DAO 3.6?

2. ADO joining two recordsets

3. Join two recordsets

4. Joining two ADO recordsets?

5. VB5-DB:INNER JOIN on two crosstab recordsets?

6. Querying two recordset objects or Joining tables from different datastores

7. Joining two tables from two databases

8. Here's a sticky wicket - joining two tables on two diffrent servers with ADO

9. Creating a new recordset by joining 2 existing recordsets

10. How can I build hierarchical recordset from two recordsets

11. joining two files as one

12. Joining two tables from different databases

 

 
Powered by phpBB® Forum Software