move record

Question

Post reply

move record

huy1002

SSC-Addicted

Points: 448
More actions
February 10, 2013 at 11:02 pm

#289250

Master table
ComputerName. AuditId. AuditDate.
A. 111. 1/1/13
A. 222. 2/1/13
A. 000. 2/1/13
B. 111. 1/1/13
C. 000. 1/1/13
C. 111. 2/1/13
AuditId equals to zero telling no vulnerability found on that day scan.
I need to write a query to move computer A to different table because there is no vulnerable auditId found after the 2/1/13.
Computer C won't be moved because there is another vulnerability in the next day. Please help. Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Is this what you're looking for?

insert into different_table(computername, auditid, auditdate)

select computername, auditid, auditdate

from original_table a1

where auditdate = (select max(auditdate)

from original_table a2

where a2.computername = a1.computername)

and auditid = '000';

This will move the row with auditid '000' to different_table. You can then delete it from original_table.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

huy1002 (2/10/2013)
Master table
ComputerName. AuditId. AuditDate.
A. 111. 1/1/13
A. 222. 2/1/13
A. 000. 2/1/13
B. 111. 1/1/13
C. 000. 1/1/13
C. 111. 2/1/13
AuditId equals to zero telling no vulnerability found on that day scan.
I need to write a query to move computer A to different table because there is no vulnerable auditId found after the 2/1/13.
Computer C won't be moved because there is another vulnerability in the next day. Please help. Thank you

Use DELETE with an OUTPUT clause. If you can post ddl and dml for both tables as shown in the link in my sig ("please read this"), someone will show you how.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

huy1002 SSC-Addicted Points: 448 More actions · Answer 3

Hi Eights, I want to move all the scans that are related to computer A as well, because there are no other vulnerability found after the day it is scanned with code 000.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Ed Wagner (2/11/2013)
Is this what you're looking for?
insert into different_table(computername, auditid, auditdate)
select computername, auditid, auditdate
from original_table a1
where auditdate = (select max(auditdate)
from original_table a2
where a2.computername = a1.computername)
and auditid = '000';
This will move the row with auditid '000' to different_table. You can then delete it from original_table.

This might be simpler:

DELETE FROM dbo.SourceTable

OUTPUT deleted.column1, deleted.column2, deleted.column3

INTO dbo.DestinationTable

WHERE 0 = 1 -- or whatever

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Much better than my suggestion - I didn't even consider that. I love it! Thanks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions