?
Solved

Querying changing table name

Posted on 2016-08-30
10
Medium Priority
?
53 Views
Last Modified: 2016-08-31
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
0
Comment
Question by:halifaxman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41776110
You'll need a dynamic query.
DECLARE @TableName AS sysname
DECLARE @MySelect AS VARCHAR(MAX)

SET @TableName='Metric2824'

SET @MySelect = 'SELECT * FROM ' + @TableName

EXEC(@MySelect)

Open in new window

0
 
LVL 35

Expert Comment

by:ste5an
ID: 41776125
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:

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);

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41776388
>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.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 9

Expert Comment

by:Valliappan AN
ID: 41776575
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)'
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41776710
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 ...
0
 

Author Comment

by:halifaxman
ID: 41777069
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.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41777175
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
0
 

Author Comment

by:halifaxman
ID: 41777522
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
0
 
LVL 35

Expert Comment

by:ste5an
ID: 41777627
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.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 41778014
Maybe this then?:

SELECT TOP (1) @current_table_name = name
FROM sys.tables
WHERE name LIKE 'Metric%'
ORDER BY create_date DESC /*get the last-created table*/
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question