0

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?

Tanner
  • 22,205
  • 9
  • 65
  • 83
Andre Coetzee
  • 1,260
  • 3
  • 20
  • 34

2 Answers2

1

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.

Community
  • 1
  • 1
Tanner
  • 22,205
  • 9
  • 65
  • 83
  • thanks for the reply, can this be done within the create table query or should i first create the table? the query needs to be part of the creat table query – Andre Coetzee Nov 20 '14 at 11:00
  • Well if you're going to write code to manage the data integrity, be it a trigger or some other method, then you won't be able to create them without the table existing first. If you try to reference an object that doesn't exist in a trigger you would get an error when you try to create it. So create the table first, then code against it. – Tanner Nov 20 '14 at 11:03
0

Try the below query

Select * from DeltaDatabase.dbo.OutStatus OUS
Inner Join CoreDatabase.dbo.Client CL on OUS.OutId=CL.sourceId
Rajesh
  • 1,600
  • 5
  • 33
  • 59