May 14, 2008 at 3:01 am
I have a holding table that stores data from some software. and i have a stored procedure that does a bulk insert to move the data to a different table and inserts the data into different columns depending on the value of one of the columns.
I need a way for the script to skip inserting a record if there is an error while inserting it.
any ideas?
May 14, 2008 at 4:49 am
you can use @@error or @@RowCount.
Say for example,
[highlight=""]
CREATE PROCEDURE p1
(
@LoadFlag CHAR(1)
)
AS
BEGIN
DECLARE @RowCnt INT
IF @LoadFlag = 'Y'
BEGIN
SELECT @RowCnt = COUNT(*) FROM Emp WHERE LoadFlag = 'Y'
END
IF @@RowCount = 0
BEGIN
PRINT 'No records found for BULK Insert' -- Here you can use Raiserror statement also
END
ELSE
BEGIN
INSERT INTO .....
SELECT ....
FROM Emp WHERE LoadFlag ='Y'
END
----------------------
CREATE PROCEDURE p1
(
@LoadFlag CHAR(1)
)
AS
BEGIN
DECLARE @RowCnt INT
IF @LoadFlag = 'Y'
BEGIN
SELECT @RowCnt = COUNT(*) FROM Emp WHERE LoadFlag = 'Y'
END
IF @@Error = 0
BEGIN
INSERT INTO .....
SELECT ....
FROM Emp WHERE LoadFlag ='Y'
END
ELSE
BEGIN
PRINT 'No records found for BULK Insert' -- Here you can use Raiserror statement also
END
[/highlight]
karthik
May 14, 2008 at 6:42 am
Not that I know of. Inserts happen in a batch. If one row fails the entire batch will fail.
Your best bet is validating the data before you try inserting it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply