December 19, 2011 at 6:39 am
I am trying to insert records into a table based on the link to another one:
BEGIN TRANSACTION LogDB
-------------------------------------------
--1. Archive data
-------------------------------------------
INSERT INTO dbo.Operation_Archive
SELECT
O.*
FROM
Operation O
WHERE
O.Created > DATEADD(dd,-2,GETDATE())
INSERT INTO dbo.LogEntry_Archive
SELECT
LE.*
FROM
LogEntry LE
INNER JOIN Operation_Archive OArch ON LE.OperationGUID = OArch.GUID
COMMIT TRANSACTION LogDB
With a BEGIN / COMMIT will the second insert statement be able to link to any records that the first INSERT statement may have inserted or will this not physically happen until the COMMIT statement has been executed? I am having issues with the fact that no records are being archived even though there should be.
Thanks
Steve
December 19, 2011 at 6:55 am
Hi,
If you read the data of a table that you have made changes to - you will see the changes even if they have not been committed.
To highlight this i have made this little query.
CREATE TABLE TESTTABLE
(NAME varchar(50))
INSERT INTO testtable values('Bob')
INSERT INTO testtable values('Ryan')
INSERT INTO testtable values('Chris')
BEGIN TRANSACTION
INSERT INTO testtable VALUES ('daniel')
INSERT INTO testtable VALUES ('Emma')
SELECT * FROM testtable
ROLLBACK
SELECT * FROM testtable
DROP TABLE TESTTABLE
Now the first select you will get emma and daniel back as well - however the one after the rollback they will vanish.
Hope this helps
Dan
December 20, 2011 at 4:12 am
Steve, Hi
I agree that your INSERT statements will populate based on your SELECT predicates, and [unless you have done SET NOCOUNT ON] you should get the "n-rows affected" responses to confirm. If you get "0-rows affected" then your predicates may be wrong.
Normally people would want to actually DELETE the rows from the original tables (otherwise you will have duplicate data which will pain you later), so you should consider that within your transaction.
After your COMMIT TRANSACTION LogDB statement you can SELECT to see if the rows have been written OK.
It is possible that an OUTER transaction exists and that it has been abandoned via a ROLLBACK, causing your "n-rows affected" to be washed out. You can confirm this via Profiler etc.
HTH
Dick
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply