Create a table, dynamically, in MS-SQL.

I’m currently working a project that requires reading an MS Excel spreadsheet into a database table so I needed a stored procedure in MS SQL to create a table dynamically given the database name, table name and number of columns as an input. Re-using some prior work.

Code below:

USE [database name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 2016-01-05
-- Description:	Create Element Tables.
-- =============================================
CREATE PROCEDURE [dbo].[CreateElementTables]
 @Database varchar(255),
 @TableName varchar(255),
 @NumColumns int
AS
BEGIN
	SET NOCOUNT ON;

    DECLARE @sqltext varchar(max)
	DECLARE @x int = 0

	SET @sqltext = 'CREATE TABLE ' + @Database + '.[dbo].' + @TableName + '([RowNumber][int] IDENTITY(1,1) PRIMARY KEY,'

	WHILE @x < @NumColumns
	BEGIN
	 SET @x = @x + 1
	 SET @sqltext = @sqltext + '[C' + cast(@x as varchar(2)) + '] [varchar](max) NULL,'
	END

	SET @sqltext = @sqltext + ') ON [PRIMARY]'
	SET @sqltext = REPLACE(@sqltext,'NULL,)','NULL)')

	print @sqltext
	exec(@sqltext)
END

Leave a Reply

Your email address will not be published. Required fields are marked *