Loop is adding one to the counter and it isn't getting to row 1

Transact-SQL
1

I am confused, the last count comes from a table.
The first variable is zero and then it adds one to it.
But the first select the value of first is 2 and i am confused, this is only a simple loop.

Thoughts ?

SELECT @last = COUNT(*) FROM SOMETABLE

--Testing set to 2
select @last = 2

SELECT @first = 0

WHILE(@first < @last)
PRINT 'here'
BEGIN
BEGIN TRANSACTION
SET @first += 1
BEGIN TRY

	SELECT @SOMDATE = DATA1
		   FROM SOMETABLE  WHERE EXTRACT_KEY_ID = '1' --@first 

	print @first 
	---This is not one it is two




	BEGIN	
					INSERT INTO SOME TABLE

	END
COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
Do something

END CATCH

END

2

what is the data type on EXTRACT_KEY_ID and what is the data type of @first

3

EXTRACT_KEY_ID is int and has yhe identity_specificaton on table set to is_identity yes
DECLARE
@first int

4

Two records in table
EXTRACT_KEY_ID 1
EXTRACT_KEY_ID 2

I loop around the rows starting with 1 and then last record is 2

5

I think i got it working it was erroring and going into the Error, so if it cannot insert into a record then it doesn't do the counter.
So i added the counter into the Error part aswell

6

Cool. Curious why are you using a loop for this?

7

Well it is about a few thousand records, so I have to transform the data then insert into master table, but every time it runs it fails on constraints, foreign keys, and I want to build a validation table first, so I know what data in other tables is required. I can then do the record inserts, have a table that show my bad records, then fix foreign keys and rerun it. If I just do a direct insert it fails on record 1000 and does not continue to process the rest.

8

Can you remove the loop and just do a set-based operation?

1 Like
9

I would recommend building all the rows that are referenced first.
for example lets say you have list of cars and colors

currently you have 1 for each of those entities
dbo.Colors(colorId int, Color nvarchar(50) )
--Colors
Black
Red
Dark Blue
dbo.Cars(carId int, CarMake nVarchar(50), CarModel nvarchar(50), ColorID int )
--few rows
Mercedez Benz, C500, 1

--New Data Comes in
Toyota Corolla, Magenta --Magenta does not exist
So you load Magenta first before you load cars. Otherwise it will fail

then Load cars and it should not fail with foreign key constraints. Would be interested to see how you have designed things under the hood. If you are really referencing by FK IDs or FK Strings.

10

Set based operation, I have not heard what this is...please elaborate, I am interested.

11

It means not using loops, do it all in one shot

12

Oh got it, I have to generate a report of errors before I insert...so I keep playing around

13

you dont need to do that at all by preventing any possible errors. Any items that might break the insert, you just assign them to a reference of data Unknown and report on that instead of errors. At the end you just send a report to yourself informing you "these rows refer to a row unknown please verify"

14

I will try tomorrow, checking every key constraint ahead of time will take me forever. I will let it just fail and capture that in a table. I will add an inserted command if successful, so I can see that 1000 rows inserted and 10 failed at the end of it.

15

hi

  1. insert the records in PK table the FK records which are not there !!! those that are failing
    simple NOT IN checking

  2. delete the records from FK table the PK records which are not there !!!

disable the constraints .. do this .. and then enable the constraints PK FK ...

16
  1. That is not accurate, because the row(s) the foreign key refers to do not yet exist hence the fk error
  2. Why delete records? How does that solve the fk issue?
    Disabling constraint then enabling does not solve either, in fact might be introducing new error bcs now it will complaingbwith an error
17

I tried this example

Create table Employee
( Id INT,
Name VARCHAR(255),
Salary INT Check (Salary > 0)
)
truncate table Employee

select * from Employee
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO Employee (Id, Name, Salary) VALUES (102, 'Niraj', 10000);
INSERT INTO Employee (Id, Name, Salary) VALUES (102, 'Niraj', 20000);
INSERT INTO Employee (Id, Name, Salary) VALUES (103, 'Chetan', 18000);
INSERT INTO Employee (Id, Name, Salary) VALUES (104, 'Vishal', 0);
END TRY
BEGIN CATCH
IF(@@TRANCOUNT > 0)
ROLLBACK TRANSACtION;

SELECT 'Transaction is Rollback, In Catch Block';

SELECT ERROR_NUMBER() 		AS ErrorNumber  
	      ,ERROR_SEVERITY() 	AS ErrorSeverity  
      ,ERROR_STATE() 		AS ErrorState  
      ,ERROR_PROCEDURE()        AS ErrorProcedure  
          ,ERROR_LINE() 		AS ErrorLine  
          ,ERROR_MESSAGE() 		AS ErrorMessage;

END CATCH

IF(@@TRANCOUNT > 0)
COMMIT TRANSACTION;

What i am looking for is to report every row that has an issue, and one row could have three constraint errors, and this example only returns one row.

I want it to update rows that are valid and then error on the row with all the constraint

18

In real world scenario how is your employees table being populated? Obviously not using insert into employees values etc

You might be doing this the hard way.

19

this statement misled me. I wrongly assumed foreign key constraints, but it is a check constraint

Check this out, maybe you could leverage dynamically checking check constraints. But I mean its up to you how to do it.

use sqlteam
go

if OBJECT_ID('Employee') is not null
	drop table Employee
Create table Employee
( Id INT,
Name VARCHAR(255),
Salary INT constraint CK_Employee_Salary  Check (Salary > 0) ,
Age int constraint CK_Employee_Age   check (Age < 21)
)

select con.[name] as constraint_name,
    schema_name(t.schema_id) + '.' + t.[name]  as [table],
    col.[name] as column_name,
    con.[definition],
    case when con.is_disabled = 0 
        then 'Active' 
        else 'Disabled' 
        end as [status]
from sys.check_constraints con
    left outer join sys.objects t
        on con.parent_object_id = t.object_id
    left outer join sys.all_columns col
        on con.parent_column_id = col.column_id
        and con.parent_object_id = col.object_id
order by con.name
20

I do think i am doing it the long way around.
Bascially it is a data migration from one system to another.

It takes data i.e in this example employees will come from Database A to Database B.
The employees in Database A may exist they may not, and for each employee they may have a state for address or something, and the state is a constraint, so that value must exist in Database B

So the only way i can determine is to loop around the records, do a query on state if there it is valid, move to the next record, if no state write this to the error log table for us to review this one record.

I have 5000 tables to do :slight_smile: