Replicating Primary Key value onto the Foreign key value

Transact-SQL

AngelinaPerez


Hey Guys,

I am just new to SQL Server. I have just created 2 tables Table1 and Table2. Table1 has a primary key (Table1.ID), which has been set on identity values.Both tables have different columns except the ID columns.

I have also used Table1.ID as foreign key and primary key on Table2 as Table2.ID.

I am just wondering guys if it is possible that when I enter or insert a data on Table1, can the same value (Table1.ID) be instantaneously copied to Table2.ID for the next data entry for Table2.

Please could you show me how this can be done using SQL Server Express 2005.

Top

Re: Replicating Primary Key value onto the Foreign key value

David Frommer


After you do the insert into table 1

INSERT INTO Table1 (<ColumnList>) VALUES(<ValueList>)

You can capture the newly created Identity value by doing the following

SET @Variable = Scope_Identity()

You would then use this value for the ID column when you insert the row in Table2.






Top

Re: Replicating Primary Key value onto the Foreign key value

Paul_Neilsen

Thank you very much for your response. Like I expressed I am just new to these, and I hope you do pardon my ignorance as I am still learning.

I do follow the first part of your response but kinda clueless from the
'SET @ ..........................

Thanks for your patience.



Top

Re: Replicating Primary Key value onto the Foreign key value

DaleJ

The SET statement captures the identity created when data was inserted in table 1.

That is the value to use in Table2.ID.

Code Snippet

DECLARE @Variable <datatype of ID column>

INSERT INTO Table1 (<ColumnList>) VALUES(<ValueList>)

SET @Variable = Scope_Identity()

INSERT INTO Table2 (ID, <remaining ColumnList>) VALUES(@Variable, <remaining values>)





Top

Re: Replicating Primary Key value onto the Foreign key value

Paul_Neilsen

Thank you very much for your response. I'm just wondering if there is a way the code you provided above can be incorporated into a trigger. Thanks once again.


Top

Re: Replicating Primary Key value onto the Foreign key value

David Frommer

If you want to do it in a trigger, there is a different method to use.

You would put the trigger on table1. Inside that insert trigger, you would use the virtual table named inserted which will contain a row for each row just inserted into table1, including the newly generated identity value for each row.

If you post your table create staements, and a descripttion of what you want to do, we can likely help you in writing the trigger.





Top

Re: Replicating Primary Key value onto the Foreign key value

DaleJ

Code Snippet

IF OBJECT_ID ('dbo.Table1InsertTrigger', 'TR') IS NOT NULL

DROP TRIGGER dbo.Table1InsertTrigger

GO

CREATE TRIGGER dbo.Table1InsertTrigger

ON dbo.Table1

AFTER INSERT

IF ( @@ROWCOUNT = 0 )

RETURN

INSERT INTO Table2 (ID, <remaining ColumnList>)

SELECT ID, <remaining ColumnList>

FROM INSERTED

GO



Top

Re: Replicating Primary Key value onto the Foreign key value

Paul_Neilsen

Hi,

Here is the create statement for the tables.

CREATE TABLE [T1] (
[ID] IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([T1_ID])
)
GO


CREATE TABLE [T2] (
[ID] INT NOT NULL,
[CONTACT_TYPE] VARCHAR(1),
[CONTACT_NAME] VARCHAR(40),
[CONTACT_1] VARCHAR(100),
[CONTACT_2] VARCHAR(50),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [PK_T2] PRIMARY KEY ([ID])
)
GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]
FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])
GO

Thanks for you help.


Top

Re: Replicating Primary Key value onto the Foreign key value

Louis Davidson

(Unless I am confused, which does happen) What you are wanting to do doesn't really make sense in SQL Server storage. To do this, you would be creating partial rows. It would likely be a bad practice to automatically create a row with null values that might just end up as a useless row.

What you are describing sounds more of a task that might be done in a user interface, in order to display a new row for a person to edit. If you want to create a row in T1 and T2 simultaneously, you could use a stored procedure and build the rows together in the context of a transaction.





Top

Re: Replicating Primary Key value onto the Foreign key value

Paul_Neilsen

Well, Louis. How can I get T1.ID automatically available or copied into T2.ID when I have entered data into in T2.


Top

Re: Replicating Primary Key value onto the Foreign key value

Paul_Neilsen

Hi,

Here is the create statement for the tables.

CREATE TABLE [T1] (
[ID] IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([T1_ID])
)
GO


CREATE TABLE [T2] (
[ID] INT NOT NULL,
[CONTACT_TYPE] VARCHAR(1),
[CONTACT_NAME] VARCHAR(40),
[CONTACT_1] VARCHAR(100),
[CONTACT_2] VARCHAR(50),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [PK_T2] PRIMARY KEY ([ID])
)
GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]
FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])
GO

I still have have not had this to work so far. My problem still remains how can I get T1.ID automatically available or copied into T2.ID, when I have opened the table, T2 for data entry.

Thanks you guys for your help.


Top

Re: Replicating Primary Key value onto the Foreign key value

DaleJ

Here ya go.

But like Louis, I wonder why you're creating a placeholder. I would think the application would have the ID of the associated T1 entry when the user is working on the T2 entry.

Code Snippet

CREATE TABLE [T1] (

[ID] int IDENTITY(1000, 2) NOT NULL,

[NAME] VARCHAR(100),

[SERV_TYPE] VARCHAR(40),

[NWT] VARCHAR(40),

[ROWVERSION] TIMESTAMP,

CONSTRAINT [T1_ID] PRIMARY KEY ([ID])

)

GO

CREATE TABLE [T2] (

[ID] INT NOT NULL,

[CONTACT_TYPE] VARCHAR(1),

[CONTACT_NAME] VARCHAR(40),

[CONTACT_1] VARCHAR(100),

[CONTACT_2] VARCHAR(50),

[ROWVERSION] TIMESTAMP,

CONSTRAINT [PK_T2] PRIMARY KEY ([ID])

)

GO

ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]

FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])

GO

IF OBJECT_ID ('dbo.Table1InsertTrigger', 'TR') IS NOT NULL

DROP TRIGGER dbo.Table1InsertTrigger

GO

CREATE TRIGGER dbo.Table1InsertTrigger

ON dbo.T1

AFTER INSERT

AS

IF ( @@ROWCOUNT = 0 )

RETURN

INSERT INTO T2 (ID)

SELECT ID

FROM INSERTED

GO

insert into t1( [NAME] , [SERV_TYPE], [NWT] )

values ('Name1', 'Service1', 'what is nwt ')

select * from t1

select * from t2





Top