Introduction

How to Create A Dynamic Table with Dynamic Name and Parameter in SQL Server. To Use Create A Dynamic Form As per Your Website Requirement to reference of this code.

Step 1: Create A Table Name:

  1. DECLARE @ptablename VARCHAR(max) = ( SELECT CONVERT(VARCHAR(50), 'tbl' + tm1.name + tm2.name + tm3.name + CONVERT(VARCHAR(10), tm3.ID)) AS tableName
  2. FROM dbo.tbltemp1 tm1 WITH ( NOLOCK ) ,
  3. dbo.tbltemp2 tm2 WITH ( NOLOCK ) ,
  4. dbo.tbltemp3 tm3 WITH ( NOLOCK )
  5. WHERE tm3.ID= 1
  6. AND tm3.ID = tm1.ID
  7. AND tm1.ID = tm2.ID
  8. )

Step 2: Remove Space of Table Name:

  1. SET @ptablename = REPLACE(@ptablename, ' ', '')

Step 3: Check Table name availability and Create A Table:

  1. IF NOT EXISTS ( SELECT TABLE_NAME
  2. FROM INFORMATION_SCHEMA.tables
  3. WHERE TABLE_NAME = @ptablename )
  4. BEGIN
  5. SET @sql = 'CREATE TABLE ' + @ptablename+ '(ID INT IDENTITY PRIMARY KEY NOT NULL,UserIdentity varchar(50),tm3ID INT)'
  6. EXEC (@sql)
  7. END

Step 4: Alter Table

  1. DECLARE @intFlag INT = 1
  2. Declare @count int = ( SELECT COUNT(*)
  3. FROM dbo.tbltemp1 tm1 WITH ( NOLOCK ) ,
  4. dbo.tbltemp2 tm2 WITH ( NOLOCK ) ,
  5. dbo.tbltemp3 tm3 WITH ( NOLOCK )
  6. WHERE tm3.ID= 1
  7. AND tm3.ID = tm1.ID
  8. AND tm1.ID = tm2.ID
  9. AND tm3.isSubmited=0
  10. )
  11. WHILE ( @intFlag <= @count )
  12. BEGIN
  13. SET @sql = 'ALTER TABLE"' + @ptablename + '"ADD "' Columnname + '" VARCHAR(' + size + ') NULL'
  14. EXEC (@sql)
  15. SET @intFlag = @intFlag + 1;
  16. END