T-SQL: INSERT INTO

Question

I have the following in T-SQL:

INSERT INTO tblAttMain(Site, FirstName, LastName)
    SELECT 
        Site, FirstName, LastName
    FROM
        tblAttTmp 
    WHERE
        Site = @Site

What I am doing here is copying columns from tblAttTmp into the tblAttMain table. Note the select statement can return hundreds of rows. tblAttTmp has a primary key called ID to specify the specific ID for that record.

If for each interaction of of the select if there is an error, I like to spit out what the tblAttTmp's ID was and create a string so that I can see all of the ID's which need to be fixed.

Not sure how to do as the select is a one shot deal.

what kind of errors are you talking about?

Jeremy Gray
– Jeremy Gray

2012-04-23 20:44:27 +00:00
Commented Apr 23, 2012 at 20:44 — Jeremy Gray
– Jeremy Gray, Commented Apr 23, 2012 at 20:44

Adam Robinson · Accepted Answer · 2012-04-23 20:27:25Z

4

As you say, select and insert are "one shot". You have two real choices:

Do each row individually. This is not only bad, it's also a pain
Determine what "errors" could occur (primary/unique/foreign key conflicts, other constraint violations, etc.) and check the data for errors before trying to insert.

(You really really need to use the second option).

answered Apr 23, 2012 at 20:27

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nate Pet Over a year ago

is there anyway in sql to get the error info per record after it attempts and insert?

Adam Robinson Over a year ago

@NatePet: SQL will stop (and roll back) the insert when it encounters an error. You'll see error information for the first failed record it tried to insert; any errors that would have occurred for subsequent rows won't show up since they won't be tried.

Tejs · Accepted Answer · 2012-04-23 20:28:59Z

0

It sounds like you would rather implement this solution as a cursor. Using the cursor, you can do each insert one at a time instead of as a bunch and do things during each iteration.

DECLARE @Cursor CURSOR FOR
SELECT Site, FirstName, LastName FROM tblAttTmp WHERE Site = @Site

DECLARE @CurrentSite <DataType>
DECLARE @CurrentFirstName <DataType>
DECLARE @CurrentLastName <DataType>

OPEN @Cursor

FETCH NEXT FROM @Cursor INTO @CurrentSite, @CurrentFirstName, @CurrentLastName

WHILE @@FETCH_STATUS = 0
BEGIN
   -- INSERT using @CurrentSite, @CurrentFirstName, @CurrentLastName
   -- Use a Try/ Catch block to catch errors

   FETCH NEXT FROM @Cursor INTO @CurrentSite, @CurrentFirstName, @CurrentLastName
END

CLOSE @Cursor
DEALLOCATE @Cursor

answered Apr 23, 2012 at 20:28

Tejs

41.4k10 gold badges71 silver badges89 bronze badges

2 Comments

Adam Robinson Over a year ago

An memory table would be simpler to look at and would probably perform better, but this does work. Or, even better, the OP could be proactive about checking the data for errors before trying to modify the data at all.

Tejs Over a year ago

Very true. Making cursors can be very error prone and it's just slightly ridiculous how much code you have to write for what would be a simple concept, but I guess that is the penalty for not thinking in set based operations.

Collectives™ on Stack Overflow

T-SQL: INSERT INTO

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related