0

I have a staging table without any constraints in my Azure SQL database (Azure SQL database 12.0.2000.8). I want to insert the data from the Staging table into the "real" table on which multiple constraints are set. When inserting the data, I use a statement of the kind

INSERT INTO <someTable> SELECT <columns> FROM StagingTable;

Now I only get the first error when violating some constraints. However, for my use case, it is important to get all violations, so they can be resolved altogether.

I have tried using TRY...CATCH mechanisms, however, this will throw an error on the first error and run the catch clause, but it will not continue with the other data. Note that the correct data that has no violations should not be inserted, so the whole insert statement can be rolled back on one error, however, I want to see all violations to be able to correct them all without having to run the insert statement multiple times to get all errors.

EDIT: The types of constraints that need to be checked are foreign key constraints, NOT NULL constraints, duplicate keys. No casting is done, so no need to check for conversions.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Lennart
  • 383
  • 4
  • 16
  • 1
    What's the SQL Server version? What types of constraints? Types conversion or foreign keys violations? – gotqn Oct 28 '20 at 08:46
  • It is an Azure SQL Database. We cannot choose the version as it is a PaaS component. However, Microsoft assures it always runs on the most recent version. I read that the lower version is purely for compatibility reasons – Lennart Oct 28 '20 at 09:07
  • OK, there is a (significant) difference between Microsoft SQL Azure 12.0.2000.8 and Microsoft SQL Server 12.0.2000.8; the former will be using the latest features because the build numbers are different (it's a different product). – Thom A Oct 28 '20 at 09:16

2 Answers2

2

There are couple of options:

  1. If you want to catch row level information, you have to go for cursors or while loop and try to insert each row in TRY CATCH block and see if you are getting any error, and log the same.

  2. Create another table similar to main table(say, MainCheckTable) with all constraints and disable all the constraints and load the data.

Now, you can leverage DBCC CHECKCONSTRAINTS to see all the constraint violations.Read more on this .

USE DBName;
DBCC CHECKCONSTRAINTS(MainCheckTable) WITH ALL_CONSTRAINTS;
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
0

First, don't look at your primary table(s). Look at the related tables e.g. lookups etc. Populate these first. Once you have populated the related tables (i.e.) satisfy all related constraints, then add the data.

You need to work backwards from the least constrained tables to the most constrained if that makes sense.

You should check that your related tables have the required reference values/fields that you intend to insert. This is easy to do, since you already have a staging table.

Craig Gers
  • 544
  • 3
  • 9
  • We get the data from an external contractor. The problem is that sometimes, this data is not correct and we need to go back to them with all violations in the data. We already work from the least constrained tables, however, it is still possible that the external contractors made an error when referencing another table. Therefore, we still need to check this. Doing a lookup requires knowledge of the tables, however, the whole solution should work automatically using dynamic SQL hence lookups beforehand are not really possible. – Lennart Oct 28 '20 at 09:01