CODEWITHSUNDEEP
sql-servert-sql
Nate Pet
Nate Pet

Reputation: 46322

T-SQL: INSERT INTO

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.

Upvotes: 0

Views: 1699

Answers (2)

Tejs
Tejs

Reputation: 41266

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

Upvotes: 0

Adam Robinson
Adam Robinson

Reputation: 185703

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

  1. Do each row individually. This is not only bad, it's also a pain
  2. 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).

Upvotes: 4

Related Questions