We help IT Professionals succeed at work.

How do I concatenate recordsets?

ndwHombre
ndwHombre asked
on
458 Views
Last Modified: 2012-06-27
Does anyone have an example of how to concatenate 2 recordsets?  Is this possible? I want to query to different databases and then combine the 2.  Would this be better to set them to arrays and then concat the 2 arrays?
Comment
Watch Question

b0lsc0ttIT Manager
CERTIFIED EXPERT

Commented:
ndwHombre,

Do you want to actually combine the values or get one recordset (or result) with fields from different but related tables?  There can be a big difference and various options depending on these details.

SQL queries let you use JOIN (various types of join) to combine tables to get a result.  The resulting fields won't have values from combined fields but you can easily get the info you want just with the query even if it is in various tables.

If you actually need to create "new" values based on those from the tables then you could use an array.  I would suggest you also consider a Dictionary object or even a temporary recordset object you make for this.  Arrays are very limited in ASP/vbscript and may not provide the options you will need for what you want.  If that is the case then the other 2 options will use more resources but can be very nice and powerful.  If you want a recommendation then you will need to let me know more about the data and what you will need to do with it.

Let me know if you have any questions or need more information.

b0lsc0tt

Author

Commented:
The data will be pulled from 2 (or more) separate database that have the same schema so changing the sql would be of no use.  There is a table on each with the same name and columns so I will essentially use the same select statement on both dbs.  Does this make sense?  I'll look into dictionary and temp rs objects.
b0lsc0ttIT Manager
CERTIFIED EXPERT

Commented:
I don't know that you need to dismiss SQL as the answer so quickly.  Besides JOIN there is UNION and that might be perfect for what you want, if I understood you right.  If you have 2 tables with similar fields and want the results from both then look at UNION as you look at the other stuff.

There is a nice example of it at http://www.w3schools.com/sql/sql_union.asp .  Let me know if you have a question.  Let me know what you think of these or how they work.

bol

Author

Commented:
I don't think you're understanding me right :)  I have two databases, an oracle server and a MS Sql Server on 2 separate servers.  They each have a database w/ the same schema.  I'll run "Select * from Table1" on both.  So in the end, I'll have 2 recordsets.
b0lsc0ttIT Manager
CERTIFIED EXPERT

Commented:
Thanks for clarifying.  I did misunderstand you although I can't understand how as I reread your posts.  It is just too late here now and I guess wasn't clear before.  Thanks for the post.  Union and Join are great but won't help much with difference servers, connections and databases (not just tables).

Let me know if you have a question about the other options though.

bol
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
By the way. If you merge recordsets, it is important that the result-recordset is a disconnected recordset. Because you don't want data from database2 to be added to database1


Set oRS = Server.CreateObject("ADODB.Recordset")
oRS.CursorLocation = 3		 ' adUseClient
oRS.Open sSQL, oConnection, 3, 4 ' adOpenStatic, adLockBatchOptimistic
Set oRS.ActiveConnection = Nothing

Open in new window

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.