Insert Data and read all Foreign Keys/Constraints first

Question

Insert Data and read all Foreign Keys/Constraints first

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

Just remember, while this may be tough, constraints are what keep us from getting dirty data, and you are already dealing with dirty data. Cleaner data means better analysis. Better analysis means better decisions for the organization. While it may be painful in the short term, cleaning this stuff up is the right way to go.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

TRACEY-320982 wrote:

Hi sschasing, I doing a few inserts first, and then a larger conversation starts from source to target. I thought it may be simple to do a few inserts but it is becoming harder due to the constraints.
Having code just error wasn't good, so now I can use the begin try and capture the error.
Going forward, I want to speed this up by looking ahead and read constraints and give an overall output of all errors before any inserts get executed.

You already know which columns of the tables are going to need to meet the constraints of the new, single table. If it were me, I'd write the code to load the data from a source table into a staging table, and have do search exclusion searches between that and the already know FK tables to produce a list by row number of the things that would violate the FKs.

Another possibility is to write some code to export the data in the 2000 tables in the "native" format using BCP. Then, setup a BULK INSERT into a staging table with the same FK constraints as the final table along with sequestering of error rows during the BULK INSERT and allow it to make up to 2 billion errors (another setting in BULK INSERT). It'll give you a list of rows and the data from the rows of things that violate the FK constraints without causing an expensive rollback. It won't tell you all of the constraints that were violated but it will quickly give you a list of rows that have at least one violation that you need to work on.

Since it'll all be done in a staging table, you can operate with relative impunity to get thing fixed before inserting the data in its final table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101120 More actions · Answer 3

You'll want to add ERROR_MESSAGE() at least to your CATCH code. That will give you the specific error msg SQL issued. You should probably go ahead and add ERROR_LINE(), although often it's not really that useful.

For example, here's roughly the code changes needed to support that:

CREATE TABLE ...

[ERROR_MSG] [nvarchar](1024) NULL,

...other code as you originally had...

DECLARE @first AS INT = 1, @Last AS INT = 1, @STATUS_DESCRIPTION VARCHAR(100)

DECLARE @error_message nvarchar(1024), @error_line int

...other code as you originally had...

BEGIN CATCH

SELECT @error_message = ERROR_MESSAGE(), @error_line = ERROR_LINE()

...other code as you originally had...

--use @error_message in place of @@ERROR in your insert into the message table

...other code as you originally had...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101120 More actions · Answer 4

That code is for one row, so whatever values you are INSERTing for the current row contains the key(s).

For multiple rows / a batch of rows, yes, you would initially not be able to identity the specific keys. But you could, for example, go thru the failed batch one row at a time to find the specific errors.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 5

Thanks, for some reason when i ran my new code it did the errors and capture in my new table...the one that should have inserted, it didn't write to my capture table as valid.

I will keep trying.............