How to use variable as table name in select into statement?

Question

I have a problem with treating table name as variable as I need to put the results to different table each month automatically (without using any advanced procedures to make this query dynamic). Can somebody help me to modify this code and make it work?

declare @exp_dte as date;
set @exp_dte='2015-12-31';
print (@exp_dte);

declare @tab_mth as nvarchar(max);
set @tab_mth=year(@exp_dte)*100+month(@exp_dte);
print (@tab_mth);

declare @tab_name as nvarchar(max)
set @tab_name='mis_anl.dbo.BIK_' + @tab_mth
print (@tab_name);


IF OBJECT_ID (N'@tab_name', N'U') IS NOT NULL
begin
    drop table @tab_name
end

select distinct
*
into @tab_name
from table_x

Answer 1

You have to use dynamic SQL to set name at runtime:

DECLARE @exp_dte  DATE    = '2015-12-31';
DECLARE @tab_name SYSNAME = '[dbo].' + QUOTENAME('BIK_' + FORMAT(@exp_dte, 'yyyyMM'));

IF OBJECT_ID (@tab_name, N'U') IS NOT NULL
BEGIN
   EXEC('DROP TABLE' +  @tab_name);
END

DECLARE @sql NVARCHAR(MAX) = N'SELECT DISTINCT *
                               INTO @tab_name
                               FROM table_x';

SET @sql = REPLACE(@sql, '@tab_name', @tab_name);

EXEC [dbo].[sp_executesql] @sql;

LiveDemo

Remarks:

1. Try to be more conscise
2. You could use FORMAT to get yyyyMM (SQL Server 2012+)
3. Always QUOTENAME generated identifiers to avoid SQL Injection attacks

I strongly recommend to read The Curse and Blessings of Dynamic SQL especially CREATE TABLE @tbl.

Answer 2

use dynamic sql ,you cant user table names as variables

declare @exp_dte as date;
set @exp_dte='2015-12-31';

declare @tab_mth as nvarchar(max);
set @tab_mth=year(@exp_dte)*100+month(@exp_dte);


declare @tab_name as nvarchar(max)
set @tab_name='mis_anl.dbo.BIK_' + @tab_mth


declare @sql1 nvarchar(max)
set @sql1='drop table '+@tab_name;

IF exists(select 1 from information_schema.tables where table_name=@tab_name)
begin
   exec(@sql1);
   end

declare @sql nvarchar(max)
set @sql='
select distinct
*
into '+@tab_name+'
from table_x'

exec (@sql)