linking tables from other databases?

[misscrf]

In a database, I am creating a new db. From there, I am setting up the
tables, so that I can eventually create a front end (usually access,
but I may attempt to be brave and lose the shell.) Anyway, I want to
use a table, read-only for a lookup. It exists in another database on
our system. Is there a way for me to link it into this database that I
am working on? I would think of it like in access when you go to do a
new table and you choose to link the table from somewhere else. That
is what I want to do. Can anyone possibly step me through this?
I know basics of sql server and like to learn.

Thanks!

[Russ Rose]

"misscrf" <mi*****@yahoo.comwrote in message
news:11**********************@a75g2000cwd.googlegr oups.com...

In a database, I am creating a new db. From there, I am setting up the
tables, so that I can eventually create a front end (usually access,
but I may attempt to be brave and lose the shell.) Anyway, I want to
use a table, read-only for a lookup. It exists in another database on
our system. Is there a way for me to link it into this database that I
am working on? I would think of it like in access when you go to do a
new table and you choose to link the table from somewhere else. That
is what I want to do. Can anyone possibly step me through this?
I know basics of sql server and like to learn.

Thanks!

Not sure if this is what you need:

This is what I call a tunnel view because it creates a tunnel to pull the
the data through from one database to another. It is not explicitly
read-only, but it will not lock, nor be affected by locks on the source
table.

USE Database2

CREATE VIEW dbo.Table1

AS

SELECT * FROM Database1.dbo.Table1 WITH (NOLOCK)
The same can be done between servers by linking the servers and adding a
third prefix to the table name.

USE Database2

CREATE VIEW dbo.Table1

AS

SELECT * FROM Server1.Database1.dbo.Table1 WITH (NOLOCK)

[AlterEgo]

misscrf,

You can use a view to reference a table in another database as follows:

create view MyView as
select Column1, Column2 ...
from MyOtherDatabase.dbo.MyTable

--or to inherit the schema (owner) use:

from MyOtherDatabase..MyTable

Grant only read privledges on this view.

- Bill

"misscrf" <mi*****@yahoo.comwrote in message
news:11**********************@a75g2000cwd.googlegr oups.com...

In a database, I am creating a new db. From there, I am setting up the
tables, so that I can eventually create a front end (usually access,
but I may attempt to be brave and lose the shell.) Anyway, I want to
use a table, read-only for a lookup. It exists in another database on
our system. Is there a way for me to link it into this database that I
am working on? I would think of it like in access when you go to do a
new table and you choose to link the table from somewhere else. That
is what I want to do. Can anyone possibly step me through this?
I know basics of sql server and like to learn.

Thanks!

[dmarkle]

Or, in SQL Server 2005, you can use a SYNONYM to accomplish the same
thing, which IMO is a bit more elegant. Check out the CREATE SYNONYM
docs...

http://msdn2.microsoft.com/en-us/ms177544.aspx

-Dave
AlterEgo wrote:

misscrf,

You can use a view to reference a table in another database as follows:

create view MyView as
select Column1, Column2 ...
from MyOtherDatabase.dbo.MyTable

--or to inherit the schema (owner) use:

from MyOtherDatabase..MyTable

Grant only read privledges on this view.

- Bill

"misscrf" <mi*****@yahoo.comwrote in message
news:11**********************@a75g2000cwd.googlegr oups.com...

In a database, I am creating a new db. From there, I am setting up the
tables, so that I can eventually create a front end (usually access,
but I may attempt to be brave and lose the shell.) Anyway, I want to
use a table, read-only for a lookup. It exists in another database on
our system. Is there a way for me to link it into this database that I
am working on? I would think of it like in access when you go to do a
new table and you choose to link the table from somewhere else. That
is what I want to do. Can anyone possibly step me through this?
I know basics of sql server and like to learn.

Thanks!