July 7, 2009 at 7:33 am
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:
July 7, 2009 at 7:48 am
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
July 7, 2009 at 8:20 am
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:
July 7, 2009 at 8:33 am
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
July 7, 2009 at 9:01 am
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:
July 7, 2009 at 11:48 am
One more question actually.. I just thought of this.
Could I put all these updates and inserts in one TRY / CATCH block?
--
:hehe:
July 7, 2009 at 11:53 am
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
July 7, 2009 at 1:52 pm
Awesome, thanks for all your help. Really appreciate it.
--
:hehe:
July 7, 2009 at 5:36 pm
No problem.
CEWII
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply