Help me with writing a few lines of code..

Question

Help me with writing a few lines of code..

mw_sql_developer

SSCoach

Points: 19445
More actions
April 9, 2015 at 10:03 am

#295799

I have a script that I need to add as a job to run daily. At the end of the run I would like to to save the results to a separate table.
I like the table name to have the current date at the end. As you see I have to manually add the date.
I am sure we can write some code to create the ACRC_443_mmddyyyy table based on today's date.
Select * INTO ACRC_443_04092015 FROM #acrc443;

Viewing 8 posts - 1 through 8 (of 8 total)

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

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

I'd suggest more direct control. Write a statement at the beginning that explicitly creates the table using the structure and name you want and then INSERT...SELECT instead of SELECT...INTO.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

True but I do have to figure out how to rename the table and include the current date as a part of the table name.

I am not going to be running this manually. I like the script to dynamically create a table each day.

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 3

Something like?

DECLARE @table_name varchar(50),

@create_sql varchar(1000),

@insert_sql varchar(1000)

SET @table_name = 'TEST_DATE_' + CONVERT(varchar, getdate(), 112)

SET @create_sql = 'CREATE TABLE ' + @table_name + '(col_one datetime)'

SET @insert_sql = 'INSERT INTO ' + @table_name + ' SELECT getdate()'

EXEC (@create_sql)

EXEC (@insert_sql)

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

Yes, that works ( I modifed according to my needs )

DECLARE @table_name varchar(50),

@sql varchar(1000);

SET @table_name = 'TEST_DATE_' + CONVERT(varchar, getdate(), 112)

Set @sql = 'Select * INTO ' + @table_name + ' from #acrc443; '

EXEC( @sql);

THANKS - Please close this post

Sean Lange SSC Guru Points: 286609 More actions · Answer 5

Why do need to make a new copy of the same table every single day? It would seem to be a lot less headache (and clutter) to add a column to hold the process date and use the same table every day.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mw_sql_developer SSCoach Points: 19445 More actions · Answer 6

mw_sql_developer

SSCoach

Points: 19445

April 9, 2015 at 1:37 pm

#1789993

Thank you and please close this ticket.

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 7

mw112009 (4/9/2015)
Thank you and please close this ticket.

This is a forum, not a help desk ticketing system, just throwing that out there.