How do I dynamically create the table with the current date at the end ?

Question

How do I dynamically create the table with the current date at the end ?

mw_sql_developer

SSCoach

Points: 19445
More actions
February 12, 2015 at 12:07 pm

#294678

Folks
I am running a script by the end of the day. What I need is the rows in my temp table get saved in a permanent table.
The name of the table should end with the current date at the end.
Can you please correct the following script please ?
Declare @tab varchar(100)
set @tab = 'MPOG_Research..ACRC_427_' + CONVERT(CHAR(10), GETDATE(), 112 )
IF object_id(@tab ) IS NOT NULL
DROP TABLE '@tab';
Select * INTO @tab from #acrc427;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Please don't. Use a single table with an additional column to define the date of the data. Otherwise, you'll end with way too many tables for no reason.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Well I need it. I can certainly update the script to remove yetersdays table.

But please I need the help.

Thx

Lowell SSC Guru Points: 323518 More actions · Answer 3

create a table with a known name, and then rename it.

declare @newname varchar(30) = 'ACRC_427_' + CONVERT(CHAR(10), GETDATE(), 112 )

IF object_id('stagingtmp' ) IS NOT NULL

EXEC('DROP TABLE stagingtmp');

IF object_id(@newname ) IS NOT NULL

EXEC('DROP TABLE ' + @newname);

select top 5 *

into stagingtmp

from sys.tables

exec sp_rename 'stagingtmp',@newname

--the day this was run, the tablename was ACRC_427_20150212

sELECT * FROM ACRC_427_20150212

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!