Check for existing records in a table

Question

Check for existing records in a table

Slick84

SSCertifiable

Points: 5602
More actions
July 7, 2009 at 7:33 am

#97833

Hi,
I have a table (Table A) in which I need to insert records from a view. If there are existing records in Table A, then I have two columns, IsArchived (bit data type and initially set to a 0) & ArchiveDate (initially null) which get updated by setting IsArchived to 1 and ArchiveDate with the current date only for these existing records while the new data is inserted with the IsArchived set to 0 and ArchiveDate set to null as said above.
What would be the best way to approach this when building a stored procedure? How would I be able to check for existing records in a table ? Could I just do the below:
IF isArchived = '0'
BEGIN
UPDATE Table A SET isArchived = 1, ArchiveDate = getDate() where isArchived = 0 and ArchiveDate = null
END
ELSE
INSERT INTO Table A VALUES 'blah', 'blah', 'blah', '0', null
Thanks in advance for any inputs.
-Slick
--
:hehe:

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I can see a couple solutions, but I still need a bit more information, is the sproc operating on all data in the table or just a particular record, your example actually suggests both..

All data:

UPDATE dbo.sometable SET IsArchived = 1, ArchiveDate = GETDATE() WHERE IsArchived = 0 OR ArchiveDate IS NULL

INSERT dbo.sometable ( field1, field2, field3, field4, field5 )

SELECT v.field1, v.field2, v.field3, 0, NULL

FROM dbo.someview v

WHERE NOT EXISTS ( SELECT 'X' FROM dbo.sometable t WHERE t.Field1 = v.Field1 )

Single Record probably just on the insert:

INSERT dbo.sometable ( field1, field2, field3, field4, field5 )

SELECT v.field1, v.field2, v.field3, 0, NULL

FROM dbo.someview v

WHERE NOT EXISTS ( SELECT 'X' FROM dbo.sometable t WHERE t.Field1 = v.Field1 )

AND v.Field1 = @PassField1

Questions?

CEWII

Slick84 SSCertifiable Points: 5602 More actions · Answer 2

Thanks so much. That makes total sense. I'm not sure why I'm using the IF statement... Can I just run the update and then the insert?

Btw, i'm trying to check all rows.

So... let's say... I have a sproc and I need to update/insert 6 tables from 6 views (1 view to 1 table, eg. View A to Table 1, View B to Table B, View C to Table C, etc.)

Can I do it like...

Update Table A ... where isArchive = '0' and ArchiveDate IS NULL

Insert into Table A...new data

Update Table B ... where isArchive = '0' and ArchiveDate IS NULL

Insert into Table B...new data

Update Table C ... where isArchive = '0' and ArchiveDate IS NULL

Insert into Table C... new data

ETC.....

--
:hehe:

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I don't see any reason why not, the code treats this as basically a set operation, the update I used had an OR instead of an AND, but that is just cus I'm paranoid, I would want the record tagged even if another process didn't get it exactly right.. The insert select uses a correlated sub-query to filter out existing records, it lets the DB engine do what it does best..

I would point out (although I think you know this) that the UPDATE must come before the Insert/Select..

CEWII

Slick84 SSCertifiable Points: 5602 More actions · Answer 4

Great, thank you so much for the help. I have changed my query to the above.

Also, I think the "OR" does seem to make more sense as its always better to be more careful.

--
:hehe:

Slick84 SSCertifiable Points: 5602 More actions · Answer 5

One more question actually.. I just thought of this.

Could I put all these updates and inserts in one TRY / CATCH block?

--
:hehe:

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

I don't see why not, but I would say that the update is a pretty safe script, it is unlikely to fail given the datatypes specified. I would say your greater risk is in the Insert/Select, if the view changes you could have a datatype mismatch which would kill the process. If you want to make sure these are always done together you could use the SET XACT_ABORT ON and begin a transaction right before the update and if there are no errors after the Insert/Select (checking @@ERROR) then you can commit the transaction. Keep in mind if the view references any table that is not local to the server a distributed transaction would be enlisted.

CEWII

Slick84 SSCertifiable Points: 5602 More actions · Answer 7

Awesome, thanks for all your help. Really appreciate it.

--
:hehe:

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

Elliott Whitlow

SSC Guru

Points: 102402

July 7, 2009 at 5:36 pm

#1021399

No problem.

CEWII