CODEWITHSUNDEEP
sql-server
DannyG1980
DannyG1980

Reputation: 1

SQL queries where the database name will change

I currently have external software that is regularly updated and as it is new databases are created (I cannot control this) the naming structure is:

I can use the system table to identify the most up to date one but how do I refer to this as the active database as the tables within it will not change?

Eg. a table valid.learner exists in both but I want it to always use the last DB created

Is this possible?

EDIT I will be querying various databases all located on the same server. all databases have static structures within them so it is just the DB name I am trying to resolve.

Thanks for current responses I think the easiest to try is to copy the DB it self and rename it

Upvotes: 0

Views: 543

Answers (3)

Rigerta
Rigerta

Reputation: 4039

You could do the following:

declare @UseStatement nvarchar(100), @DatabaseName nvarchar(128) 
select @DatabaseName = Name from sys.databases WHERE YourConditionToFindTheLatestOne) --make sure you only get one row here, only the latest db name

select @UseStatement = N'USE ' + QUOTENAME(@DatabaseName) + ';'

print @UseStatement
exec(@UseStatement)

You will need to do this at the top of every script you have, it will replace the usual static Use DatabaseName; that generally is used when you know the database name beforehand.

Upvotes: 3

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131364

The question is rather broad. A lot of information is missing, eg are there any cross-database queries, what type of processing is performed, how big is the database? Are all databases on the same server?

There are a lot of options:

  • It sounds like this database is reference data from a government application. In which case, one can just copy it to another database with a well-known name.

  • Rigerta Demiri already posted how you can execute queries against the latest database if you don't run cross-database queries, ie if you don't want to join data between your database and the application's. Just change the target database. You can do that in the connection string too.

  • You can also just copy the data from the latest database into your own, with an SSIS package. The source (ie the reference database) can be modified programmatically. It can also be specified as a package parameter.

  • You can create SYNONYMs in your database for the reference tables with CREATE SYNONYM MyTable FOR ThatDB.dbo.MyTable. This can be automated with a script that reads the latest database name, drops the old synonyms and creates new ones with the latest database.

Which is best depends on the data volume, type of data, how often it changes, how it is used, how much time you are willing to spend on maintenance etc

UPDATE

I forgot a more advanced scenario - if the data does change, read only the modified rows from the source database and copy them to the target. In other words, treat it just like any other ETL scenario. SQL Server offers lightweight change tracking since 2005, which has to be activated per-table. If you don't want to modify the source each time a new version is deployed, you'll have to drop back to checkiing timestamps or created/updated fields.

Upvotes: 1

Pawel Czapski
Pawel Czapski

Reputation: 1864

Below may help to find latest database:

select top 1 name 
from sys.databases
where name like 'FIS_SUB_0030310QGT810_700133_HULLCO_1617ILR%'
order by create_date desc

Upvotes: 0

Related Questions