How to Deal with PK/FK In case of Insert/Update
-
Hi,
Can anyone one Give me Idea on "How to Deal with Foriegn Key and Primary while Inserting Bulk Data From Other Source".
I am using information_schema.constraint_table_usage for Disable and Enable all. But this also is creating Problem. Its working for one Insert statement but not working for all in a row.
Thanks
-
Maybe you could load the data in a staging table. Then you could insert only the valid data in the production table. That would also give you a chance to create any missing rows in the Pk table and then load all the data.
-
Hi We do something similar and we have to sp one to disable FK and one to enable Fks
Heres the scripts
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qa_disablefk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[qa_disablefk]
GO
CREATE PROCEDURE dbo.qa_disablefk AS
declare @table_name varchar(32)
declare @constraint_name varchar(64)
declare @sqlstmt varchar(256)
declare c cursor for
select a.table_name,a.constraint_name from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE a,
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b
where a.constraint_name = b.constraint_name
open c
fetch next from c
into @table_name,@constraint_name
while @@fetch_status =0
begin
print @table_name
set @sqlstmt = 'alter table ' + @table_name + ' nocheck constraint ' + @constraint_name
exec (@sqlstmt)
fetch next from c
into @table_name,@constraint_name
end
close c
deallocate c
GO
GRANT EXECUTE ON dbo.qa_disablefk TO PUBLIC
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[qa_enablefk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[qa_enablefk]
GO
CREATE PROCEDURE [dbo].[qa_enablefk] AS
declare @table_name varchar(32)
declare @constraint_name varchar(64)
declare @sqlstmt varchar(256)
declare c cursor for
select a.table_name,a.constraint_name from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE a,
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b
where a.constraint_name = b.constraint_name
open c
fetch next from c
into @table_name,@constraint_name
while @@fetch_status =0
begin
print @table_name
set @sqlstmt = 'alter table ' + @table_name + ' check constraint ' + @constraint_name
exec (@sqlstmt)
fetch next from c
into @table_name,@constraint_name
end
close c
deallocate c
GO
GRANT EXECUTE ON dbo.qa_enablefk TO PUBLIC
GO
NOTE please not you may end up with orphan records.
-
An approach I used on a weekly data load a while back might be useful:
The data we received was, essentially, a change log from the true owner of the data. We might see several records with the same primary key at various points within the file. Sounds similar to your situation.
I set a baseline number of records to process at once (5000 or 10000, don't recall and no longer have access to the routine, as it belongs to a former employer). If the insert attempt failed, I checked to see if the failure was due to a duplicate key. If so, I cut the number of records being processed in half, and tried again; if necessary, until I got down to one record. Once things were successful, I bumped the number of records processed back up by doubling it for each successful run, until I hit my baseline.
Mind you, if I had it all to do over again, I would probably at least consider some sort of pre-processing step.
R David Francis -
Hi Everyone,
Thanks for your suggestiona...I have have no other option then inserting Data in Child Table first and then Parent. Otherwise, It may be a problem for Application.
What to do in case i am getting Severity Levels error like Conversion of Datetime and other Data Type or some else. I don't want to come out of Procedure in any case of Error. I have to process all Records. Is there any suggestion for this. How to handle this type of Error. I have used Dynamic SQL Statement here.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply