CODEWITHSUNDEEP
sqlsql-serverjoin
Andre Coetzee
Andre Coetzee

Reputation: 1310

Inner join between different database

I want to create a table using the following script in a database called DeltaDatabase: 

CREATE TABLE [dbo].[OutStatus](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[OutId] [int] NOT NULL,
[StatusType] [varchar](255) NULL,
[StatusDate] [datetime] NULL)

I would then like to INNER JOIN a column into this table from another database called CoreDatabase.

The column name is sourceId from the table Client. So in other words OutId needs to be foreign key of SourceId.

How do I join that column into my OutStatus table from the other database using the create table script?

Upvotes: 0

Views: 459

Answers (2)

Tanner
Tanner

Reputation: 22743

The basic syntax to retrieve data would be:

SELECT * 
FROM CoreDatabase.dbo.Client c
INNER JOIN DeltaDatabase.dbo.OutStatus os ON c.SourceId = os.OutId

You need to fully qualify the tables name with: DatabaseName.Schema.TableName

You may wish to limit the columns or add a where clause to reduce the data that is returned.

As far as creating a foreign key across databases goes, it's not something you can do. You would have to use triggers or some other logic to maintain referential integrity between the primary and foreign keys.

Upvotes: 1

Rajesh
Rajesh

Reputation: 1620

Try the below query

Select * from DeltaDatabase.dbo.OutStatus OUS
Inner Join CoreDatabase.dbo.Client CL on OUS.OutId=CL.sourceId

Upvotes: 0

Related Questions