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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy