SQL Server: Create tablename with dates assigned dynamically between specified dates

I needed a script to create tables with dates assigned. For example if needed a table to be created like
abc_01Jun2017
abc_02_Jun2017
abc_03_Jun2017
abc_04_Jun2017
So I will have to pass 4 parameters i.e. (a) SchemaName (b) TableName (c) StartDate (d) EndDate
In the above case the parameters would be (a) SchemaName = 'myschema' (b) TableName  = 'abc' (c) StartDate= '06/01/2017' (d) EndDate = '06/04/2017'

So I have created a script to perform this
USE [MyDatabase]
GO

--select CONVERT(VARCHAR(11),GETDATE(),106)
--select Replace((select CONVERT(VARCHAR(11),GETDATE(),106)),' ','')

Declare @str varchar(5000)
Declare @TableName varchar(100)
Declare @SchemaName varchar(100)
Declare @StartDate Date
Declare @EndDate Date

SET @SchemaName = 'myschema'
SET @TableName = 'abc'
SET @StartDate = '06/01/2017'
SET @EndDate = '06/04/2017'

Declare @NoOfDays int
SET @NoOfDays = (SELECT Datediff(day,@StartDate,@EndDate)) + 1

Declare @StartDatestr varchar(20)
Declare @EndDatestr  varchar(20)

Declare @Counter int
SET @Counter = 1

While (@Counter<=@NoOfDays)

BEGIN

SET @StartDatestr = Replace((select CONVERT(VARCHAR(11),@StartDate,106)),' ','')

Declare @sql1 As varchar(4000)
set @sql1 = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'+''''+@SchemaName+'.'+@TableName+'_'+@StartDatestr+''') AND TYPE IN (N'+'''U'''+')) BEGIN
DROP TABLE ' + Quotename(@SchemaName+'.'+@TableName+'_'+@StartDatestr) + ' END'

Exec (@sql1)

SET @str = 'create Table '+@SchemaName+'.'+@TableName+'_'+@StartDatestr +'
(
ID int -- We should specify required column names
)'


Exec (@str)

SET @Counter = @Counter + 1
SET @StartDate = DATEADD(day,1,@StartDate)

END



 

1 comment:

  1. Butterfly clicking involves two fingers, mainly the index and the middle finger, and you have to slam both of your fingers alternately on the LMB ( left mouse button) at a steady speed. Butterfly clicking can increase the cps rate up to 16cps! (as per the user experience), which is ideal for Minecraft PVP. Space bar clicker

    ReplyDelete