Need Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need Help

Author

Topic

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-06-05 : 04:59:15

[code]
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[/code]

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-06-05 : 07:23:15

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-05 : 08:31:39

In the CATCH block, add this to the WHERE Clause:


AND STATE_CD Not IN (select StateCode from state

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-06-05 : 09:02:18

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-05 : 11:06:48

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-05 : 11:29:22

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-06-16 : 04:52:24

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

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-06-16 : 06:31:36

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-16 : 11:31:24

[code]-- 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[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources