April 7, 2008 at 5:22 am
Hi there,
I use SSIS in sql server 2005 to copy two tables from DB1 to the same tables in DB2.
There is my table's code:
/*Master Table*/
CREATE TABLE [dbo].[t1](
[c1] [int] IDENTITY(1,1) NOT NULL,
[c2] [nchar](10) NULL,
CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED
(
[c1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
/*Child Table*/
CREATE TABLE [dbo].[t2](
[c12] [int] IDENTITY(1,1) NOT NULL,
[c22] [nchar](10) NULL,
[t1c1] [int] NULL,
CONSTRAINT [PK_t2] PRIMARY KEY CLUSTERED
(
[c12] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[t2] WITH CHECK ADD CONSTRAINT [FK_t2_t1] FOREIGN KEY([t1c1])
REFERENCES [dbo].[t1] ([c1])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[t2] CHECK CONSTRAINT [FK_t2_t1]
when i copy these 2 tables in DB2 new recordes will be added in the tables. so the c1 will change, but it is not updated in t2, in db2.
The Foreign key constraint is diabled in t2 (db2) so it can copy new records.
How can I update the FK in t2?
Thank u all
April 7, 2008 at 11:38 am
The two constraints at the bottom of the table creation script should do the job for you. Make sure those are run after the insert.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 10:03 pm
I just want to copy the records, the structures are the same in the both DBs
April 9, 2008 at 7:44 am
I guess I don't understand your question, then. You said you want to know how to enable the FK, but then you say it's already there ("the two structures are the same"). I must be misunderstanding what you're asking about.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply