Archive based on value in different Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Archive based on value in different Table

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-04-06 : 12:31:18

Hi There,

I am archiving some records to an archive table. Here is a snippet of code I am using which works well:
INSERT INTO tbArchive

SELECT [RecordId],
[LocationId],
[Description],
[PerComplete],
[CreateBy],
[CreateDate],
[EditBy],
[EditDate],
[ClosedBy],
[ClosedDate]
FROM tbTable
Where Percomplete = 100

But now in my where clause I need to be able to get a value from another table where the RecordId is the same as the RecordId for each record in the select code above.

How would I go about doing that in the where clause.

A value needs to exist in the second table before I can write to the archive table.

Thanks for your help,

Steve

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-06 : 13:07:56

If there are no duplicates in table2, then

SELECT a.[RecordId],
a.[LocationId],
a.[Description],
a.[PerComplete],
a.[CreateBy],
a.[CreateDate],
a.[EditBy],
a.[EditDate],
a.[ClosedBy],
a.[ClosedDate]
FROM tbTable a JOIN tbTable2 b on a.[RecordId]=b.[RecordId]

Or,

SELECT a.[RecordId],
a.[LocationId],
a.[Description],
a.[PerComplete],
a.[CreateBy],
a.[CreateDate],
a.[EditBy],
a.[EditDate],
a.[ClosedBy],
a.[ClosedDate]
FROM tbTable a where exists (select * from tbTable2 b where a.[RecordId]=b.[RecordId])

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-04-07 : 05:26:11

Hi There,
Thanks for your help so far.
Your suggestions select the right records. When I paste the code into Query Analyser the correct records are displayed but I have a problem. When I put the code into a stored procedure it only archives the first record. I need it to archive all the records that match the select criteria. Here is a snippet of the code I am using which at the moment selects around 10 records.

INSERT INTO tbTableArchive

SELECT a.[RecordId],
a.[LocationId],
a.[Description],
a.[PerComplete],
a.[CreateBy],
a.[CreateDate],
a.[EditBy],
a.[EditDate],
a.[ClosedBy],
a.[ClosedDate]
FROM tbTable a where exists (select * from tbTable2 b where a.[RecordId]=b.[RecordId])

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to tbTableArchive', 16, 1)
END

DELETE tbTable
where exists (select * from tbTable2 b where a.[RecordId]=b.[RecordId])

Thanks for your help, I appreciate it.

Best Regards,

Steve

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-07 : 06:14:43

I don't understand why it would archive just the first record. You'd need to post the sp you are using.

Steve2106
Posting Yak Master

183 Posts

Posted - 2009-04-07 : 06:30:23

Hi Sakets_2000,

I have sent an email to you.

Thanks for your help,

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources