Me again! - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Me again!

Author

Topic

sqlneofito
Starting Member

16 Posts

Posted - 2010-03-31 : 18:34:01

Another easy one that I'm having doubts:
I need to move the transactions that are older than the one that is specified by my parameter from the Production.TransactionHistory to Production.TransactionHistoryArchive table (I have an idea but not really getting there) any help will be deeply appreciated ...thank you

create proc Dave3
@TranDate datetime
as
if ( select count(*) from Production.TransactionHistory where TransactionDate < @TranDate ) = 0

begin
print 'There are no Transactions to Archive'
return
end

if ( select count(*) from Production.TransactionHistory where TransactionDate < @TranDate ) <> 0
begin
print 'There are several Transaction ready to Archive'
return
end
(THIS IS WHERE I'M CONFUSED)
exec Dave3 @TranDate = '3/18/2004'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-31 : 18:46:11

You should use IF EXISTS/NOT EXISTS instead of COUNT(*) for performance reasons. It sounds like you just need to add an INSERT statement, but I'm not exactly clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-31 : 18:47:10

Maybe this:

INSERT INTO Production.TransactionHistoryArchive(ColumnList...)
SELECT ColumnList...
FROM Production.TransactionHistory
WHERE TransactionDate < @TranDate

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-31 : 18:48:18

Also, please provide an actual subject for any future topics you start. "Me again!" could get ignored, since it is not descriptive of your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlneofito
Starting Member

16 Posts

Posted - 2010-03-31 : 18:52:59

Thank you and I'll keep in mind to use a better title.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-31 : 19:02:09

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources