CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Blackstar
Blackstar

Reputation: 161

Get the wrong line ID in ms sql

I have an INSERT statment wich inserts large amount of data into tableA from tableB.
Here is a very simple code example:

INSERT [dbo].[tableA]
SELECT   field1 [field_1] 
FROM [dbo].[tableB]
WHERE [codeID] IN (SELECT [codeID] FROM #tempTable WHERE RecordMarker = 1)

There is a temporary table wich holds codeIDs (at least 1 or more) needed to insert to tableA.

But there would be incorrent data in tableB what cannot be inserted into tableA. For example an numberic(30,2) field cannot map to numeric(13,2). In this case I get an excetpion and the statement has been terminated.

How can I get the CodeID or the wrong line number in tableB if I get an error? Now I have just the error message but no line number.

For example: Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.

EDIT: There are more than one field in the table with different field types. So the numeric type is just an example.

Upvotes: 0

Views: 116

Answers (2)

Branden Schwartz
Branden Schwartz

Reputation: 181

If you know the type of the destination field you're having the issue with, in this case a numeric of (13,2) precision, you can run a SELECT with a TRY_CONVERT on the potential problem field against your temp table and filter for NULL results. You could add a WHERE clause to your insert statement if you wanted to ensure that it would run successfully and not try to insert those "bad" rows. 

CREATE TABLE #t (x NUMERIC(30,2),field2 varchar(10))
INSERT INTO #t 
SELECT 123456789.23,'x'
UNION
SELECT 12345678901212343.23,'y'
UNION
SELECT 12345678923523523235.23,'z'
UNION
SELECT 42.0, 'a'
SELECT *, TRY_CONVERT(NUMERIC(13,2),x,1) [Converted to numeric(13,2)] FROM #t

Reference: http://msdn.microsoft.com/en-us/library/hh230993.aspx

Upvotes: 1

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

Please try the following:

INSERT [dbo].[tableA]
SELECT   field1 [field_1] 
FROM [dbo].[tableB]
WHERE [codeID] IN (SELECT [codeID] FROM #tempTable WHERE RecordMarker = 1)
AND [codeID] <= 9999999999999.99;

INSERT ErrorLog
SELECT *
FROM [dbo].[tableB]
WHERE [codeID] > 9999999999999.99;

Upvotes: 1

Related Questions