Post reply

Linking tables

  • October 20, 2008 at 7:18 am

    #194745

    I’ve had a web site since 1996. I use four Access databases to provide stock and option information to subscribers. My process is to download data from the markets in four .cvs files which are FTPed to my server after the markets close each day. The four files are LINKED to Access tables at the server and then a number of Queries perform calculations on the data. Everything has worked fine for years. Now my service provider is suggesting switching to SQL Server, saying it will speed things up. He’s offered to do the switch for many dollars. As an old, retired engineer, I’ve always done my own stuff. I’ve got SQL Server 2005 and have started the conversion only to find that I may not be able to link tables. I’ve searched SQL Server Central for how to do this without luck. So my first question to this group is “Can tables be linked to facilitate what I am doing?”

    Buzz

  • October 20, 2008 at 7:24 am

    #887090

    What sort of tables link you need?

  • October 20, 2008 at 7:33 am

    #887098

    The closest thing to a linked table in SQL is a Synonym. You should look these up. However, I would suggest you do not have 4 different databases and you just create one database. If you have conflicting object names, you could use Schemas to keep them apart.

    Here is the bigger question - is something broken or too slow? Unless you are not meeting a requirement of some sort, I would recommend you do not re-write this. A platform change is a major revision.

  • October 20, 2008 at 7:38 am

    #887105

    I'd like to FTP the .cvs tables to the remote server (at my host) and have them be used by the Queries at the host to be used by various .asp pages at my site. The Queries contain rather complex algorithms that do calculations and generate results tables.

    I'm off to the soup kitchen to help out and will return this afternoon.

    Buzz

  • October 20, 2008 at 7:39 am

    #887107

    Hi Buzz

    The short answer is Yes, they can: it's called Server Linking in BOL. It can be fiddly and may not work depending upon the csv format of the files. You should also consider importing the csv files into SQL Server staging tables - there are a number of ways of doing this and the advantage is that, after a relatively slow import step, everything afterwards is native SQL Server so is simpler and quicker.

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply