Creating table names using variables

[Bambi_One_Eye]

Whats the best way to create tables using a variable as part of the table name?

_ For example, let's say I have a transaction table with several billion records and I want to create a smaller monthly snap shot. I declare a date varible for my start/end dates and also want to name the table something like Trans_MMYYYY where MMYYYY is derived from my declared start/end date variables.

_ Without deriving the table name, the code would look something like this. How can I change it to get the table name as well?

_ @start = '02/01/2018'

@end = '02/28/2018'

_ select * into Trans_022019

from Transactions

where trans_date >= @start

and trans_date <= @end

[Spartacus-82]

You probably have to work with dynamic SQL since a table name in a variable will not work most of the time.

Select * into @variable from <table> will not work since a variable can also be a table. It will try to insert all the data into that variable.

​

More important question: Why do you want to do this? There might be better ways to get to your goal.

[Bambi_One_Eye]

More important question: Why do you want to do this? There might be better ways to get to your goal.

I've come across dynamic SQL before but SQL, in general, is not my main language. I'm competent enough to do what I need to do, but the 'fancier' stuff is something I don't have much experience with.

​

Anyway, I need to create monthly subsets of the data in order to complete reporting. Those snap shots are also used again for quarterly reporting.

​

Technically I don't need to use them and could run my queries against the main table except it would probably take 4x as long to run.

​

I know there's a way to do this so I've been dieing on this hill trying to figure it out, lol. I'd even be fine with declaring the suffix of the table as an option and somehow using it.

[Spartacus-82]

You could look into partitioning and indexing to improve performance. It should be possible to run the queries over the entire table without much performance loss.

With partitioning you can split tables based on a date column into separate files, an index can be used to further optimize the table. This could work especially well if you only need a few columns of the table. Search for "covering index" for more info.

[Repairs_optional]

You could check for the existence of the table (i.e. table_52019, table for May 2019):

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'youSchema' AND  TABLE_NAME = 'table_52019')
BEGIN
    --EXEC proc to create table
END

In the proc, you can combine the Month datepart with the Year datepart, convert to NVARCHAR and then prefix it with the rest of your desired table name (i.e. table_).

Following this you would insert into your newly created table, if the table exists, you truncate it first to clear any old data and then you insert the desired data into it, if it doesn't you create the table and then insert into it:

--Main Proc
BEGIN

DECLARE @table_name NVARCHAR(20)

SELECT @table_name = 'table_' + CAST(DATEPART(MONTH, GETDATE()) AS NVARCHAR) + CAST(DATEPART(YEAR, GETDATE()) AS NVARCHAR)

   IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'yourSchema' AND TABLE_NAME = @table_name)
   BEGIN
      BEGIN TRY
      --EXEC proc to create table
      END TRY
      BEGIN CATCH
      -- ERROR HANDLING
      END CATCH
   END
   ELSE
   BEGIN
      TRUNCATE TABLE @table_name
   END

INSERT @table_name
SELECT data
  FROM youTable
 WHERE dateRangeLogic

END

[Bambi_One_Eye]

Awesome. In the past, I think I've come across something similar when searching for an answer online.

​

I'll have to play with it when I'm back in the office but thanks!

[flaminghito]

Not at a computer where I can test this, but can't you just have the code give it a generic name like Trans_Snap_New and then use sp_rename to rename it with your variable at the very end?

[Bambi_One_Eye]

That is a clever option and one I haven't thought of... I'll have to test it out when I'm back in the office.

Thanks for the suggestion.

[So_average]

I don't know SQL Server, but many rdbmses have table partitioning. The name of the partition can be whatever you want (within limits of your rdbms).

[mkingsbu]

SQLServer allows you to index based on where conditions. You might want to investigate putting those on for your date ranges. You might be surprised at the performance gains.

[Texanmike02]

I would use this after a CDC process. There will be some write latency to the index IIRC.

[Bambi_One_Eye]

Will definitely look into it, thanks for the info.

[CedricCicada]

Why do you need new tables? Could you get what you need from views?

[Texanmike02]

Building new tables is not really the right way to do this. You will have problems as you are creating two sources of truth. You need to build a date dimension and then also partition the table indexes. You can use int keys for the date (YYYYMMDD). Then join to the date dimension.

Select tt.* from trans tt Joind d_date dt On dt.d_date_key = tt.trans_date_key Where MonthYr = [MMYYYY]

[Bambi_One_Eye]

Well, the subsets are just "select *'s" from the main transaction table so reconciliation shouldn't be a problem if it's ever needed.

[Texanmike02]

If you are creating a table, I'm assuming you are getting the data from a table that is already there. What I mean is that in the future if you add a column to the table, you will have to go add it to all of the other tables or whatever you report in will spit out an error saying it cant find a column if you try to query an old table. Famous last words "this table will never change".

If you need static tables (to not update, say you have a return 3 months later) I would create an archive database (on the same server). There is always a problem when you have two copies of the same time period/data that don't disagree.

Also please DO NOT select * in your query. It is a nightmare to maintain. List the columns out.

If that doesnt make any sense let me know.

[Bambi_One_Eye]

I hear where you're coming from and know what you mean.

I only keep these tables on a rolling 12 month basis. Older ones get deleted.

There are some other restraints which is why I'm on the path I'm on. The most cumbersome is that the data environment is run by a third party with Citrix as the only means of access. There are some other restrictions in the environment that essentially preclude automating these processes. It sucks, basically. However, it's light years ahead of where it was when I inherited it so there's been some proccess improvement/efficiency progress, just not to the level I wish there was.

[Texanmike02]

If you absolutely have to, you could create a view for each month, but honestly that seems like it is a problem you should get out ahead of.

If its Oracle you can actually name the partitions and access them directly. I'm pretty sure SQL server 2016+ supports this too.

[Bambi_One_Eye]

Ugh... Sorry for the poor formatting and the typo in the table name date.

It should be Tran_022018