Delete records from multible databases

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

Transact-SQL (2005)

Delete records from multible databases

Author

Topic

jay882
Starting Member

5 Posts

Posted - 2008-10-29 : 17:41:21

I need to delete records from 4 partition databases. The problem I am having is the names of the databases change every week based on date.

The first step I have is to get the name of the databases

USE DB_DEV_WS
SELECT DB_NAME /* This select returns the list of current partition databases for the inner select */
FROM PARTITIONS
WHERE DELETED = 0 IN
(
SELECT *
FROM wsdb10_10_06.dbo.SUMMARY_URL /* The database name on this line changes weekly */
WHERE USER_ID IN
(
SELECT USER_ID FROM DB_DEV_WS.dbo.USERS
WHERE USER_LOGIN_NAME IN
(
'name1', 'name2', 'name3'
)
)
)
GO
(
SELECT *
FROM wsdb10_10_13.dbo.SUMMARY_URL /* The database name on this line changes weekly */
WHERE USER_ID IN
(
SELECT USER_ID FROM DB_DEV_WS.dbo.USERS
WHERE USER_LOGIN_NAME IN
(
'name1', 'name2', 'name3'
)
)
)
GO

How do I get the result from the first select for the DB_NAME and put in into the inner select automatically? and then delete the records in each database.

Jay

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-29 : 18:08:55

The databasenames change every 4 weeks?
Who decided on this environment?

E 12°55'05.63"
N 56°04'39.26"

jay882
Starting Member

5 Posts

Posted - 2008-10-30 : 10:41:43

There are 4 databases 1 for each week in the month. When a database reaches the 5th week it is dropped. I inherited this problem...lol I did not design it.

The first part, the database name does not change. DB_DEV_WS is a "master" DB for this app. This is where I get the names of the current databases. I need to be albe to insert the names of the current databses in the second part of this. - SELECT * FROM wsdb10_10_06.dbo.SUMMARY_URL

Jay

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-10-30 : 10:50:38

Madness

jay882
Starting Member

5 Posts

Posted - 2008-10-30 : 16:26:26

Anyone?

Help!

Jay

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-30 : 16:51:13

[code]
USE DB_DEV_WS
declare @DB_NAME varchar(8000)

SELECT @DB_NAME = DB_NAME /* This select returns the list of current partition databases for the inner select */
FROM PARTITIONS
WHERE DELETED = 0 IN
(
SELECT *
FROM wsdb10_10_06.dbo.SUMMARY_URL /* The database name on this line changes weekly */
WHERE USER_ID IN
(
SELECT USER_ID FROM DB_DEV_WS.dbo.USERS
WHERE USER_LOGIN_NAME IN
(
'name1', 'name2', 'name3'
)
)
)

EXEC('
SELECT *
FROM '+@dbname+'.dbo.SUMMARY_URL
WHERE USER_ID IN
(
SELECT USER_ID FROM DB_DEV_WS.dbo.USERS
WHERE USER_LOGIN_NAME IN
(
''name1'', ''name2'', ''name3''
)
)'
)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources