Newbie to SQL :(

[prodtest]

Hi All,
I have bee thrown in at the deep end with a SQL task, all because I have experience on Access!

Here it goes:

We have a view called quote_products on a SQL server in a database called live.
Also on the same server have a database called quotes, in the quotes database is a table called products.

What I need to do is create the table so that every time it is opened it refreshes with the data from the view quote_products in the live database.

Any help is really appreciated

Thanks
Ben

 

[simian101]

A view is current query representation of data based on a table.

It should be up to date.

Please give more details.

Simi

 

[prodtest]

Hi Simian,
Thanks for responding.
I was hoping it would be as simple as point our quoting software at the view from the other database, however it wont allow you to look at a view in another database only the table that it is already setup to use.
I was hoping by setting up the table to update from the view I could achieve the same outcome.

THanks
Ben

 

[simian101]

You also might want to include an example of what you are trying to do.

Simi

 

[prodtest]

I will try and explain some more.

Basically we have a sales quoting system that takes all the product information from a database table situated on our SQL server.
On the same server we have a SAGE1000 system that contains a table that has all our stock information (part nos, stock levels, etc etc). The idea is to have the quoting system use the data stored in out SAGE100 system so it always shows the update products and associated information.

I was hoping to be able to simply point the quoting system to the database table. Unfortunatley the quoting system will not allow you to take product data from another source other than its own product information table. This includes a view

My thought was to create a view in the database and have the table update from it every time it is opened or queried from. Alternatively I was hoping to create some sort of routine that would update the table every night.

Hope this makes sense.

Thanks

Ben

 

[simian101]

If I understand, you have some quoting software that is looking at a database called quotes with table called products.

There is some data that your application needs from a view called quote_products from a database called live.

Is it a report or a query or what exactly are you needing to do with data.

Also, I guess the data from Live is maintianed by some other program?

Simi

 

[gmmastros]

What version of SQL Server? If you are using SQL2005 (or higher), I would encourage you to do a little research on SYNONYMS.

Basically, you could create a synonym for the table in the other database so that it appears to be the table in the original database. It's a little confusing to describe, but do your research. If you're using sql2005, I think this approach will work well.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[prodtest]

Thanks for that, I am using 2005, so will look into that first.

Thanks again.

Ben