Multiple Recordsets

Results 1 to 3 of 3

Thread: Multiple Recordsets

  1. #1
    Join Date
    Dec 1969

    Default Multiple Recordsets

    I have two databases.<BR>One Local Database and the second Remote database.<BR>I want to "insert into Local select * from Remote" .<BR>Obviously I need two connections and two recordsets.<BR>But how to do it? I mean should both the connections remain open? How can I refer a particular table of a particular Database. I have not get any concrete answer since last two weeks,I had written this in other forums of aspmessageboard too, it would be a great help if someone can write some mock codes . This might be simple for some of you, but somehow I find it difficult and I haven&#039;t got much of help.<BR>Thank you and Best Regards,<BR>Nilabdhi

  2. #2
    Join Date
    Dec 1969

    Default What kind of DB?

    Or Databases? Is one SQL Server and one Oracle? OR or or or or????<BR><BR>It&#039;s hard to give you concrete answers to a very incomplete question.<BR><BR>&#062; Obviously I need two connections and two recordsets<BR><BR>It is *NOT* obvious, at all! If both databases are Access, for example, you do NOT want or need two connections. Access allows you to work with an external database from a single connection.<BR><BR>Probably, SQL Server does, as well, though I don&#039;t use SQL Server and don&#039;t know for sure.<BR><BR>In any case, if you *DID* have TWO connections, you could *NEVER* do it via "Insert Into Local Select * From Remote" because a single SQL statement can only work with ONE CONNECTION at a time. (Which is probably why Access was extended to work with external DBs, to bypass the SQL limitation.)<BR><BR>If you can&#039;t find some solution whereby the DB system you are using allows reference to an external DB, then you are probably stuck with moving one record at a time from one DB to the other. Slow and tedious, but very easy to write the code.<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default RE: Multiple Recordsets

    For SQL Server, if you want to import the first 5 records from the Employees database in the sample Northwind DB into the current SQL Server database as a table named "PrettyCool"<BR><BR>Select TOP 5 * into PrettyCool from northwind.dbo.employees<BR><BR>Note that the reverse will work too, however, if the dbs are on separate servers and you are remoting, the table referenced by into must be local to the server (though not necessarily in the same db)<BR><BR>For MS Access, if you connected to northwind you can do this to copy the first 5 employee recordsinto a new database:<BR><BR>SELECT TOP 5 * INTO PrettyCool IN &#039;c:data arget.mdb&#039; FROM Employees<BR><BR>I am not sure if you can do the reverse in ms access and use the IN clause on your FROM.<BR><BR>hope this helps,<BR><BR>Dave Kawliche<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts