How can I dynamically set the table name using SELECT INTO?

Question

DROP TABLE Backup_LOAD_EMPLOYEE
SELECT * INTO dbo.Backup_LOAD_Employee FROM LOAD_Employee WHERE 1=1
TRUNCATE TABLE LOAD_Employee

I am bulk inserting employee data from external source . In my sp each time after import , I will truncate the load_employee table. Before truncate I would like to take a table backup,previous day data should truncate .

how to give auto increment table name ( in an SP)?

Pondlife · Accepted Answer · 2013-05-10 14:29:00Z

1

This doesn't answer your question directly (but you can use dynamic SQL), but a better solution is probably to put the backup date into a column, instead of creating one table per day. Then you can more easily query the archived data for multiple days, because it's all in one table. Something like this:

create table dbo.Backup_LOAD_Employee (
    BackupDate date,
    --- other columns
    )
go

insert into dbo.Backup_LOAD_Employee (BackupDate, ...)
select cast(getdate() as date), ... -- other columns
from dbo.LOAD_Employee

truncate table dbo.LOAD_Employee

answered May 10, 2013 at 14:29

Pondlife

16.3k6 gold badges40 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RAGKV Over a year ago

Thanks for the info. Main idea behind this is to retain the backup table for a day very next day it will it should dropped. As the its contain huge data.

Pondlife Over a year ago

I would still use a column for the date. It's highly likely that at some point you'll need to use it, even if it's only for development/debugging purposes.

Collectives™ on Stack Overflow

How can I dynamically set the table name using SELECT INTO?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related