Select into variable table name

Question

Select into variable table name

bswhipp

Ten Centuries

Points: 1117
More actions
November 3, 2022 at 11:53 am

#4108155
I want to select data from one table into another table. The to table name has to be variable bases on the year. Basically:
Thanks for any help.
```
Declare @table varchar(20)
set @table = 'SalesPerformance' + cast(year(current_Timestamp)-1 as varchar(4))
print @table

Select * into @table from SalesPerformance
```

Viewing 6 posts - 1 through 5 (of 5 total)

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

bswhipp Ten Centuries Points: 1117 More actions · Answer 1

Got it.

Declare @table varchar(20)
set @table = 'SalesPerformance' + cast(year(current_Timestamp)-1 as varchar(4))

Declare @Sql varchar(max)

set @Sql = 'Select * into ' + @table + ' from SalesPerformance'

Exec (@Sql)

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

Quick thought, this can be simplified

😎

Example:

DECLARE @TSQL NVARCHAR(MAX) = CONCAT(N'SELECT * INTO ',(SELECT QUOTENAME('SalesPerformance' + CAST(YEAR(CURRENT_TIMESTAMP)-1 AS VARCHAR(4)))), N' FROM SalesPerformance;')
SELECT (@TSQL);

Further, I advise you to look into and use sp_executeSQL for executing dynamic T-SQL 😉

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Another simplification would be to use DATENAME for the year instead of casting the results of the YEAR(() function.

EDIT: Sorry... I missed the -1 that was being done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

To continue, one of my pet peeves are SQL lines of code that go off screen and dynamic SQL that comes out as a single line of code, especially when trying to troubleshoot. I also prefer "token replacement" because it's easier to troubleshoot than having to worry about getting things like single quote right amidst formulas, etc. It's not too bad on this simple example but a simple example is a good place to demo. 😀

With that being said, this is how I'd do it if it were me... I used "PRINT" instead of sp_Execute to demo with...

DECLARE @TSQL NVARCHAR(4000) = REPLACE(N'
 SELECT *
   INTO dbo.SalesPerformance<<YYYY>>
   FROM dbo.SalePerformance
;'      ,N'<<YYYY>>',CONVERT(NCHAR(4),DATEPART(yy,GETDATE())-1))
;

PRINT @TSQL
;

Here's the result from that.

Makes troubleshooting much larger code a whole lot easier. Of course, if you have user inputs coming in, they need to be deloused along with the proper use of SP_EXECUTESQL as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 5

Great example on how naming variables ( etc ) can be confusing and lead you totally out of focus

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me