SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/05/2014 :  04:59:15  Show Profile  Reply with Quote

Hi All,
I have two databases (DB1 and DB2) & CITY table in both databases. Here, i need to load CITY table data from DB1 to DB2; it has 50000 records. 

DB1 has no Foreign Key(FK) relationships among tables; where DB2.dbo.CITY table is having FK to DB2.dbo.STATE table.

If any error occurs while looping through the DB1.dbo.CITY table data for populating DB2.dbo.CITY, it should display the failed record to the end  user. 
The below code is working fine if NO ERROR occurs.

I will explain the issue with sample data. Assume that the below 20 records are in table DB1.dbo.CITY; the 14th record is failing against the FK, then entire 1st batch of records ( 1 to 10) are moving to CATCH. 
But i would like to get only the ONE failed record which is causing data inconsistency for FK.


Table:  DB1.dbo.City
ZIP_CODE	CITY_NAME	STATE_CD
705	AIBONITO	PR
610	ANASCO	PR
611	ANGELES	PR
612	ARECIBO	PR
601	ADJUNTAS	PR
631	CASTANER	PR
602	AGUADA	PR
603	AGUADILLA	PR
604	AGUADILLA	ZZ
605	AGUADILLA	PR
703	AGUAS BUENAS	PR
704	AGUIRRE	PR
7675	WESTWOOD	NJ
7677	WOODCLIFF LAKE	NJ
7885	WHARTON	NJ
7981	WHIPPANY	NJ
7999	WHIPPANY	NJ
8888	WHITEHOUSE	NJ
8889	WHITEHOUSE STATION	NJ
7095	WOODBRIDGE	NJ

Table: DB2.dbo.City

ZIP_CODE	CITY_NAME	STATE_CD
--------    ----------  ---------



--Below is the query which is used for loading table data from DB1 to DB2

DECLARE @Count DECIMAL(10,2) ,@Min_Record INT,@Max_Record INT,@No_Of_Records INT
DECLARE @ErrorData( PrimaryKeyValue VARCHAR(100))
SELECT @Count = 1,@Min_Record = 1,@Max_Record = 10;

SELECT @No_Of_Records = COUNT(*) FROM MedChive..CITY

SET @Count = CEILING(@No_Of_Records / 10.00) 

WHILE( @Count > = 1)
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION 
		
		INSERT INTO DB2.dbo.CITY (ZIP_CODE, CITY_NAME, STATE_CD)
                OUTPUT inserted.ZIP_CODE INTO @ErrorData 
			SELECT ZIP_CODE, CITY_NAME, STATE_CD
			FROM(SELECT ROW_NUMBER() OVER  (ORDER BY ZIP_CODE) AS RowNumber,ZIP_CODE, CITY_NAME, STATE_CD 
				FROM DB1.dbo.CITY)a
				WHERE A.RowNumber between @Min_Record and @Max_Record; 
		COMMIT TRANSACTION

		SELECT   @Count= @count-1,@Min_Record = @Max_Record + 1 ,@Max_Record = @Max_Record + 10;
	END TRY
	BEGIN CATCH
		DECLARE @error VARCHAR(1000) = ERROR_MESSAGE()
		SELECT 'DB1.CITY','DB2.CITY',PrimaryKeyValue,ERROR_MESSAGE(),'Insert',SUSER_SID(),GETDATE() FROM @ErrorData;

		SELECT   @Count= @count-1,@Min_Record = @Max_Record + 1 ,@Max_Record = @Max_Record + 10;
	END CATCH
END


The above query returns 1 to 10 records ( i.e. 1st batch). But i would like to see only one record that is 9th record of sample data.

--
Chandu

Edited by - bandi on 06/05/2014 07:27:24

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/05/2014 :  07:23:15  Show Profile  Reply with Quote
below is the consumable format of sample data and expected output:

CREATE TABLE STATE ( StateCode char(2) primary key)
INSERT INTO STATE(STATECODE) VALUES ( 'PR');
INSERT INTO STATE(STATECODE) VALUES ( 'NJ');

--CITY1 with FK
CREATE TABLE CITY1 (ZIP_CODE varchar(10), CITY_NAME varchar(100), STATE_CD char(2) references STATE(StateCode))


--CITY table without FK
CREATE TABLE CITY (ZIP_CODE varchar(10), CITY_NAME varchar(100), STATE_CD char(2) )
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '705', 'AIBONITO', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '610', 'ANASCO', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '611', 'ANGELES', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '612', 'ARECIBO', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '601', 'ADJUNTAS', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '631', 'CASTANER', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '602', 'AGUADA', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '603', 'AGUADILLA', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '604', 'AGUADILLA', 'ZZ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '605', 'AGUADILLA', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '703', 'AGUAS BUENAS', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '704', 'AGUIRRE', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '7675', 'WESTWOOD', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '7677', 'WOODCLIFF LAKE', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '7885', 'WHARTON', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '7981', 'WHIPPANY', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '7999', 'WHIPPANY', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '8888', 'WHITEHOUSE', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '8889', 'WHITEHOUSE STATION', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '7095', 'WOODBRIDGE', 'NJ');


GO
--Query to populate CITY1 from CITY table
DECLARE @Count DECIMAL(10,2) ,@Min_Record INT,@Max_Record INT,@No_Of_Records INT

SELECT @Count = 1,@Min_Record = 1,@Max_Record = 10;
SELECT @No_Of_Records = COUNT(*) FROM CITY
SET @Count = CEILING(@No_Of_Records / 10.00) 
WHILE( @Count > = 1.00)
BEGIN
	BEGIN TRY
		INSERT INTO CITY1 (ZIP_CODE, CITY_NAME, STATE_CD)
		SELECT ZIP_CODE, CITY_NAME, STATE_CD 
		FROM(SELECT ROW_NUMBER() OVER  (ORDER BY ZIP_CODE) AS ZipCode,ZIP_CODE, CITY_NAME, STATE_CD 
			FROM dbo.CITY)a
			WHERE A.ZipCode between @Min_Record and @Max_Record; 
	END TRY
	BEGIN CATCH		
		DECLARE @Error varchar(100) = ERROR_MESSAGE()
		SELECT 'CITY' OldTab,'CITY1' NewTab,ZIP_CODE,@Error ErrorMsg,'Insert' OPType,GETDATE() 
		FROM (SELECT ROW_NUMBER() OVER  (ORDER BY ZIP_CODE) AS ZipCode,ZIP_CODE
				FROM dbo.CITY)a
				WHERE A.ZipCode between @Min_Record and @Max_Record; 
	END CATCH
	SELECT   @Count= @count-1,@Min_Record = @Max_Record + 1 ,@Max_Record = @Max_Record + 10;
END

--DELETE CITY1

/*OUTPUT is 
OldTab	NewTab	ZIP_CODE	ErrorMsg	OPType	(No column name)
CITY	CITY1	601	The INSERT statement conflicted with the FOREIGN KEY constraint "FK__CITY1__STATE_CD__2AAB3E11". The	Insert	2014-06-05 16:49:20.587
CITY	CITY1	602	The INSERT statement conflicted with the FOREIGN KEY constraint "FK__CITY1__STATE_CD__2AAB3E11". The	Insert	2014-06-05 16:49:20.587
CITY	CITY1	603	The INSERT statement conflicted with the FOREIGN KEY constraint "FK__CITY1__STATE_CD__2AAB3E11". The	Insert	2014-06-05 16:49:20.587
CITY	CITY1	604	The INSERT statement conflicted with the FOREIGN KEY constraint "FK__CITY1__STATE_CD__2AAB3E11". The	Insert	2014-06-05 16:49:20.587
CITY	CITY1	605	The INSERT statement conflicted with the FOREIGN KEY constraint "FK__CITY1__STATE_CD__2AAB3E11". The	Insert	2014-06-05 16:49:20.587
CITY	CITY1	610	The INSERT statement conflicted with the FOREIGN KEY constraint "FK__CITY1__STATE_CD__2AAB3E11". The	Insert	2014-06-05 16:49:20.587
CITY	CITY1	611	The INSERT statement conflicted with the FOREIGN KEY constraint "FK__CITY1__STATE_CD__2AAB3E11". The	Insert	2014-06-05 16:49:20.587
CITY	CITY1	612	The INSERT statement conflicted with the FOREIGN KEY constraint "FK__CITY1__STATE_CD__2AAB3E11". The	Insert	2014-06-05 16:49:20.587
CITY	CITY1	631	The INSERT statement conflicted with the FOREIGN KEY constraint "FK__CITY1__STATE_CD__2AAB3E11". The	Insert	2014-06-05 16:49:20.587
CITY	CITY1	703	The INSERT statement conflicted with the FOREIGN KEY constraint "FK__CITY1__STATE_CD__2AAB3E11". The	Insert	2014-06-05 16:49:20.587
*/


--But I want below output. Beacuse the Zip_CODE 604 record is having incorrect State_CD
OldTab	NewTab	ZIP_CODE	ErrorMsg	OPType	(No column name)
CITY	CITY1	604	The INSERT statement conflicted with the FOREIGN KEY constraint "FK__CITY1__STATE_CD__2AAB3E11". The	Insert	2014-06-05 16:49:20.587

--
Chandu

Edited by - bandi on 06/05/2014 07:23:55
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1130 Posts

Posted - 06/05/2014 :  08:31:39  Show Profile  Reply with Quote
In the CATCH block, add this to the WHERE Clause:


AND STATE_CD Not IN (select StateCode from state
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/05/2014 :  09:02:18  Show Profile  Reply with Quote
Thanks for your suggestion... But still i need better solution for this...

For sample one only i said it is FK violation.. CATCH block can trigger for any type of run-time error( not only the FK Violation)...

Actually, we have DBMS database. Now wanted to migrate DBMS database as RDBMS...

Is there any way to handle different types of run-time errors for batch of inserts?


If error occurs for one record in TRY block, it was rolling back the entire loop (i.e. 10 records). Just wanted to insert 9 correct records into destination table and display the remaining 1 record, which is causing FK violation...

Like this i want....

--
Chandu
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1130 Posts

Posted - 06/05/2014 :  11:06:48  Show Profile  Reply with Quote
If you want to handle multiple error types in the CATCH block, you'll need to examine the error to determine what to do.
e.g.



create table ref (id int identity(1,1), data int primary key)
insert into ref(data) values(41),(42),(43)

create table withfk(id int identity(1,1), fkdata int references ref(data), fkdate datetime)

begin try
insert into withfk(fkdata, fkdate) values (42, 'abc')
end try
begin catch
   declare @error varchar(1000) = error_message()
   if @error like 'The INSERT statement conflicted with the FOREIGN KEY constraint%' begin
      select @error
   end
   else if @error like 'Conversion failed %' begin
      select @error
   end
end catch



etc.

to stop the rollback, you could do it outside a transaction.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/05/2014 :  11:29:22  Show Profile  Reply with Quote
Is there any particular reason you are doing this one row at a time? It seems like it would be easier to just join to see which values do not exist and display those to the user, then load all the ones that do exist. Two statements and done.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/16/2014 :  04:52:24  Show Profile  Reply with Quote
See am not inserting one record at a time. we are inserting 10/50/100/500 records at a time. Due to one FK violation all 10 records are rollbacked. But i need to insert remaining 9 records except the violated record into Original table and then move failed record to some other table called 'FailureRecordsTable'

Could you please suggest me the solution?

If my information is not understandable i can provide you more details...

quote:
Originally posted by gbritton

If you want to handle multiple error types in the CATCH block, you'll need to examine the error to determine what to do.
e.g.



create table ref (id int identity(1,1), data int primary key)
insert into ref(data) values(41),(42),(43)

create table withfk(id int identity(1,1), fkdata int references ref(data), fkdate datetime)

begin try
insert into withfk(fkdata, fkdate) values (42, 'abc') SELECT * FROM OldTable WHERE rowNumber between 1 and 10;
end try
begin catch
   declare @error varchar(1000) = error_message()
   if @error like 'The INSERT statement conflicted with the FOREIGN KEY constraint%' begin
      select @error
   end
   else if @error like 'Conversion failed %' begin
      select @error
   end
end catch



etc.

to stop the rollback, you could do it outside a transaction.



--
Chandu
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 06/16/2014 :  06:31:36  Show Profile  Reply with Quote
Here is one solution, added @FailureRecordsTable to check for invalid state codes:

DROP TABLE CITY1, CITY, STATE

CREATE TABLE STATE ( StateCode char(2) primary key)
INSERT INTO STATE(STATECODE) VALUES ( 'PR');
INSERT INTO STATE(STATECODE) VALUES ( 'NJ');

--CITY1 with FK
CREATE TABLE CITY1 (ZIP_CODE varchar(10), CITY_NAME varchar(100), STATE_CD char(2) references STATE(StateCode))


--CITY table without FK
CREATE TABLE CITY (ZIP_CODE varchar(10), CITY_NAME varchar(100), STATE_CD char(2) )
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '705', 'AIBONITO', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '610', 'ANASCO', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '611', 'ANGELES', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '612', 'ARECIBO', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '601', 'ADJUNTAS', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '631', 'CASTANER', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '602', 'AGUADA', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '603', 'AGUADILLA', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '604', 'AGUADILLA', 'ZZ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '605', 'AGUADILLA', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '703', 'AGUAS BUENAS', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '704', 'AGUIRRE', 'PR');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '7675', 'WESTWOOD', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '7677', 'WOODCLIFF LAKE', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '7885', 'WHARTON', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '7981', 'WHIPPANY', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '7999', 'WHIPPANY', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '8888', 'WHITEHOUSE', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '8889', 'WHITEHOUSE STATION', 'NJ');
INSERT INTO CITY (ZIP_CODE, CITY_NAME, STATE_CD) VALUES ( '7095', 'WOODBRIDGE', 'NJ');


GO
--Query to populate CITY1 from CITY table
DECLARE @Count DECIMAL(10,2) ,@Min_Record INT,@Max_Record INT,@No_Of_Records INT

SELECT @Count = 1,@Min_Record = 1,@Max_Record = 10;
SELECT @No_Of_Records = COUNT(*) FROM CITY
SET @Count = CEILING(@No_Of_Records / 10.00)

DECLARE @FailureRecordsTable TABLE (ZIP_CODE varchar(10), CITY_NAME varchar(100), INVALID_STATE_CD char(2))
INSERT INTO @FailureRecordsTable (ZIP_CODE, CITY_NAME, INVALID_STATE_CD)
SELECT C.ZIP_CODE, C.CITY_NAME, C.STATE_CD
FROM CITY C
WHERE NOT EXISTS(SELECT 1 FROM STATE S WHERE C.STATE_CD = S.StateCode)

SELECT * FROM @FailureRecordsTable

WHILE( @Count > = 1.00)
BEGIN
BEGIN TRY

INSERT INTO CITY1 (ZIP_CODE, CITY_NAME, STATE_CD)
SELECT ZIP_CODE, CITY_NAME, STATE_CD
FROM(SELECT ROW_NUMBER() OVER (ORDER BY ZIP_CODE) AS ZipCode,ZIP_CODE, CITY_NAME, STATE_CD
FROM dbo.CITY)a
WHERE A.ZipCode between @Min_Record and @Max_Record
AND NOT EXISTS(SELECT 1 FROM @FailureRecordsTable F WHERE INVALID_STATE_CD = a.STATE_CD);


END TRY
BEGIN CATCH
DECLARE @Error varchar(100) = ERROR_MESSAGE()
SELECT 'CITY' OldTab,'CITY1' NewTab,ZIP_CODE,@Error ErrorMsg,'Insert' OPType,GETDATE()
FROM (SELECT ROW_NUMBER() OVER (ORDER BY ZIP_CODE) AS ZipCode,ZIP_CODE
FROM dbo.CITY)a
WHERE A.ZipCode between @Min_Record and @Max_Record;


END CATCH
SELECT @Count= @count-1,@Min_Record = @Max_Record + 1 ,@Max_Record = @Max_Record + 10;
END

SELECT * FROM CITY1

--------------------
Rock n Roll with SQL

Edited by - rocknpop on 06/16/2014 06:32:18
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/16/2014 :  11:31:24  Show Profile  Reply with Quote
-- Display non-matching rows.
SELECT 
	*
FROM 
	City
LEFT OUTER JOIN
	State
	ON City.STATE_CD = State.StateCode
WHERE
	State.StateCode IS NULL

-- Insert Macthing
INSERT
	CITY1 
	(
		ZIP_CODE, 
		CITY_NAME, 
		STATE_CD
	)
SELECT
	City.STATE_CD,
	City.CITY_NAME,
	City.STATE_CD
FROM 
	City
INNER JOIN
	State
	ON City.STATE_CD = State.StateCode
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000