adding to table name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

adding to table name

Author

Topic

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-03-01 : 16:10:38

I am creating a vb.net program which runs some sql code to backup certain tables. I am running a select into to backup all the data then clearing the main table and filling again. This will happen every night. I was thinking I could just add the string version of getdate to the table name but it didn't like it.
SELECT * INTO PG_Totals+cast(GETDATE AS VARCHAR(100)) FROM PG_Totals

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-03-01 : 16:40:36

You need to create a dynamic SQL...something along the lines of:

EXEC('SELECT * INTO PG_Totals_' + CONVERT(Varchar,GETDATE(),110 )+ ' FROM PG_Totals')

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-03-01 : 17:10:06

I've been messing around some and came up with this so far.
declare @date date
declare @datestr varchar(50)
set @date=getdate()
set @datestr=CAST(@date AS varchar(50))
set @datestr = REPLACE(@datestr,'-','.')
--print (@datestr)
EXEC('SELECT * INTO PG_Totals_' + @datestr+ ' FROM PG_Totals')

It always errors stating that... Incorrect syntax near '.03'.
Even though the print (@datestr) returns the exact string that I am trying to add to the table name.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-03-01 : 17:26:19

You cannot create a table with period in the name of the table.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 01:57:27

quote:
Originally posted by dinakar

You cannot create a table with period in the name of the table.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Unless the name is within []

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-02 : 02:28:53

What is the problem with dinakar's solution. i thinks that reached upto your requirement...

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 02:37:17

quote:
Originally posted by vaibhavktiwari83

What is the problem with dinakar's solution. i thinks that reached upto your requirement...

Vabhav T

Haven't you seen the reply?

Run this and see

CREATE table tbl_12.3(i int)

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-02 : 02:49:10

Okey so dinkars solution can be modified like

PRINT('SELECT * INTO PG_Totals_' + REPLACE(CONVERT(Varchar,GETDATE(),110 ), '-', '_') + ' FROM PG_Totals')

because using [] with table name will not be good practice because usually we do not use table name like this...

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 03:45:19

quote:
Originally posted by vaibhavktiwari83

Okey so dinkars solution can be modified like

PRINT('SELECT * INTO PG_Totals_' + REPLACE(CONVERT(Varchar,GETDATE(),110 ), '-', '_') + ' FROM PG_Totals')

because using [] with table name will not be good practice because usually we do not use table name like this...

Vabhav T

Yes. You need [] only if the name has special characters like space, .,/,etc

Madhivanan

Failing to plan is Planning to fail

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2010-03-02 : 12:08:09

hey thanks for picking that up Madhi.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-03 : 01:16:53

quote:
Originally posted by dinakar

hey thanks for picking that up Madhi.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

You are welocme
Are you very busy at work?
I don't see you very often

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources