Mark Wilson
asked on
Querying changing table name
SQL 2008
I have a table that I am querying from that keeps, changing the number on the end of it.
The last 4 digits keep changing on a daily or sometimes 2 or 3 times a day
Metric2824
Query
Select number, Created
from Metric2824
I go in on a daily basis and change the numbers to the maximum number
How can I change the query so it grabs the latest 4 digit number on the Metric table?
Thanks
I have a table that I am querying from that keeps, changing the number on the end of it.
The last 4 digits keep changing on a daily or sometimes 2 or 3 times a day
Metric2824
Query
Select number, Created
from Metric2824
I go in on a daily basis and change the numbers to the maximum number
How can I change the query so it grabs the latest 4 digit number on the Metric table?
Thanks
You don't change that query. The user or process, which is creating that table is responsible for updating your view.
But Vitor is right, you need under most circumstances dynamic SQL for that. This should be the last step in the data import/table creation process:
But Vitor is right, you need under most circumstances dynamic SQL for that. This should be the last step in the data import/table creation process:
DECLARE @alterview NVARCHAR(MAX) = N'ALTER VIEW Query AS SELECT M.number, M.Created FROM ? M;';
DECLARE @tablename sysname;
SET @tablename = ( SELECT TOP 1
T.name
FROM sys.tables T
WHERE T.name LIKE 'Metric____'
ORDER BY T.name DESC
);
SET @alterview = REPLACE(@alterview, '?', @tablename);
EXECUTE (@alterview);
>I go in on a daily basis and change the numbers to the maximum number
A vastly better idea would be to keep the table name the same but add a date column that stores the date of the data. That way no one will have to use dynamic SQL to concatenate table names with the date when consuming this table.
A vastly better idea would be to keep the table name the same but add a date column that stores the date of the data. That way no one will have to use dynamic SQL to concatenate table names with the date when consuming this table.
Another option is to have virtual tables, i.e. have a common table, with a field called TableName or something like that, and let users put data in the same table, with different TableName value each time. Then you can query it easily with 'select * from commonTable where TableName = (SELECT max(TableName) from commonTable)'
You can avoid dynamic SQL if you can insure that only one copy of this code can run at one time.
Create a unique synonym for that code, then dynamically alter the synonym to match the new table name, like this:
DECLARE @current_table_name nvarchar(128)
SELECT TOP (1) @current_table_name = name
FROM sys.tables
WHERE name LIKE 'Metric%'
ORDER BY CAST(SUBSTRING(name, PATINDEX('%[0-9]%', name), 128) AS int) DESC
EXEC('IF OBJECT_ID(''dbo.MySyn'') IS NOT NULL DROP SYNONYM dbo.MySyn')
EXEC('CREATE SYNONYM dbo.MySyn FOR dbo.' + @current_table_name)
--from this point on, the code process "dbo.MySyn" as the "table" name, w/o needing dynamic SQL
SELECT *
FROM dbo.MySyn
WHERE ...
Create a unique synonym for that code, then dynamically alter the synonym to match the new table name, like this:
DECLARE @current_table_name nvarchar(128)
SELECT TOP (1) @current_table_name = name
FROM sys.tables
WHERE name LIKE 'Metric%'
ORDER BY CAST(SUBSTRING(name, PATINDEX('%[0-9]%', name), 128) AS int) DESC
EXEC('IF OBJECT_ID(''dbo.MySyn'') IS NOT NULL DROP SYNONYM dbo.MySyn')
EXEC('CREATE SYNONYM dbo.MySyn FOR dbo.' + @current_table_name)
--from this point on, the code process "dbo.MySyn" as the "table" name, w/o needing dynamic SQL
SELECT *
FROM dbo.MySyn
WHERE ...
ASKER
Hi
Thanks for the answers I have tried
DECLARE @current_table_name nvarchar(128)
SELECT TOP (1) @current_table_name = name
FROM sys.tables
WHERE name LIKE 'Metric%'
ORDER BY CAST(SUBSTRING(name, PATINDEX('%[0-9]%', name), 128) AS int) DESC
EXEC('IF OBJECT_ID(''dbo.MySyn'') IS NOT NULL DROP SYNONYM dbo.MySyn')
EXEC('CREATE SYNONYM dbo.MySyn FOR dbo.' + @current_table_name)
--from this point on, the code process "dbo.MySyn" as the "table" name, w/o needing dynamic SQL
SELECT *
FROM dbo.MySyn
I get the error message when running it
Conversion failed when converting the nvarchar value 'Metric' to data type int.
Thanks for the answers I have tried
DECLARE @current_table_name nvarchar(128)
SELECT TOP (1) @current_table_name = name
FROM sys.tables
WHERE name LIKE 'Metric%'
ORDER BY CAST(SUBSTRING(name, PATINDEX('%[0-9]%', name), 128) AS int) DESC
EXEC('IF OBJECT_ID(''dbo.MySyn'') IS NOT NULL DROP SYNONYM dbo.MySyn')
EXEC('CREATE SYNONYM dbo.MySyn FOR dbo.' + @current_table_name)
--from this point on, the code process "dbo.MySyn" as the "table" name, w/o needing dynamic SQL
SELECT *
FROM dbo.MySyn
I get the error message when running it
Conversion failed when converting the nvarchar value 'Metric' to data type int.
Then you have non-numeric chars after the first numeric char. If you always need to pick up exactly 4 digits, you can do this:
SELECT TOP (1) @current_table_name = name
FROM sys.tables
WHERE name LIKE 'Metric%'
ORDER BY CAST(SUBSTRING(name, PATINDEX('%[0-9][0-9][0-9][0-9]%', name), 4) AS smallint) DESC
SELECT TOP (1) @current_table_name = name
FROM sys.tables
WHERE name LIKE 'Metric%'
ORDER BY CAST(SUBSTRING(name, PATINDEX('%[0-9][0-9][0-9][0-9]%', name), 4) AS smallint) DESC
ASKER
Thanks for getting back to me
I have had a look at some of the older tables and not all have 4 digits which is causing the problem
Examples
Metric_Draft
Metric94
Metric961
I have had a look at some of the older tables and not all have 4 digits which is causing the problem
Examples
Metric_Draft
Metric94
Metric961
Why? You said the newer have all four Digits. Then just look for These tables. Either 'Metric____' or 'Metric[0-9][0-9][0-9][0-9 ]', depending on your nameing scheme.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window