September 27, 2012 at 10:26 am
Dear Members,
[font="Courier New"]SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
GROUP BY a.name, b.type
ORDER BY a.name, b.type[/font]
i need to store result of above query into new table (which is not already created)?
September 27, 2012 at 10:28 am
SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
INTO NewTable
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
GROUP BY a.name, b.type
ORDER BY a.name, b.type
September 27, 2012 at 10:28 am
Hi,
Use the INTO statement just before the FROM.
SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays INTO TableName
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
GROUP BY a.name, b.type
ORDER BY a.name, b.type
The query output will be sent into the table
September 27, 2012 at 10:36 am
can we write this into sub query?
what i mean is
select * into <new table> from ( <sub query>)
September 27, 2012 at 10:40 am
konuridinesh (9/27/2012)
can we write this into sub query?what i mean is
select * into <new table> from ( <sub query>)
yes,
SELECT * INTO TableName FROM (SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
GROUP BY a.name, b.type) t
Have to remove ORDER BY since it's not supported on sub queries...
September 27, 2012 at 10:40 am
declare one temp table and then insert the data which u r selecting
insert into @temp
(
name,
LastSuccessfulBackup,
IntervalInDays
)
SELECT a.name, b.type, MAX(b.backup_finish_date) LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
GROUP BY a.name, b.type
ORDER BY a.name, b.type
September 27, 2012 at 10:42 am
konuridinesh (9/27/2012)
can we write this into sub query?what i mean is
select * into <new table> from ( <sub query>)
Why do you want to do that?
September 27, 2012 at 10:51 am
what my exact requirement is
i need record backup details for each database on instance into table
September 27, 2012 at 10:53 am
thanks pimane
September 27, 2012 at 10:58 am
Have a look at sp_MSForEachDB.
It's an undocumented sp which runs code against each db. There are plenty of examples if you search this site.
September 27, 2012 at 11:10 am
thanx laurie
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply