August 18, 2016 at 10:05 pm
Hi , i have a requirement to create a table on the fly as part of the store proc by grabbing the column names and data types from another table that has 2 columns(column_name and column_datatype), any idea?
Lets say i have TableA(column1,column2) with 100 records from which i have to create TableB with 100 columns from the values of TableA, hope that helps. Thanks
August 18, 2016 at 11:42 pm
Quick example, recommend you add constraints to the data type columns to prevent malicious code injection.
😎
USE TEEST;
GO
SET NOCOUNT ON;
--
IF OBJECT_ID(N'dbo.TBL_TEST_DYNAMIC_CREATE') IS NOT NULL DROP TABLE dbo.TBL_TEST_DYNAMIC_CREATE;
-- Create the table definition table
CREATE TABLE dbo.TBL_TEST_DYNAMIC_CREATE
(
TCD_ID INTIDENTITY(1,1) NOT NULL CONSTRAINT PK_dbo_TBL_TEST_DYNAMIC_CREATE_TCD_ID PRIMARY KEY CLUSTERED
,TCD_COL_NAME NVARCHAR(128) NOT NULL
,TCD_DATA_TYPE NVARCHAR(128) NOT NULL
);
-- Sample data
INSERT INTO dbo.TBL_TEST_DYNAMIC_CREATE ( TCD_COL_NAME , TCD_DATA_TYPE )
VALUES (N'COL_01',N'INT')
,(N'COL_02',N'INT')
,(N'COL_03',N'INT')
,(N'COL_04',N'INT')
,(N'COL_05',N'INT')
,(N'COL_06',N'VARCHAR(50)')
,(N'COL_07',N'VARCHAR(150)')
;
-- Table name from GUID
DECLARE @TABLE_NAME NVARCHAR(128) = N'TTBL_' + REPLACE(CONVERT(VARCHAR(128),NEWID(),0),NCHAR(45),N'');
-- Create table template
DECLARE @TEMPLATE NVARCHAR(MAX) = N'
CREATE TABLE [dbo].{{@TABLE_NAME}}
(
{{@COLUMNS}})
;
'
DECLARE @CREATE_TABLE_STR NVARCHAR(MAX) =
REPLACE(
REPLACE(@TEMPLATE,N'{{@COLUMNS}}',
STUFF(
(
SELECT
NCHAR(44) + QUOTENAME(DC.TCD_COL_NAME) + NCHAR(32) + DC.TCD_DATA_TYPE + NCHAR(13) + NCHAR(10)
FROM dbo.TBL_TEST_DYNAMIC_CREATE DC
FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(MAX)'),1,1,N''))
,N'{{@TABLE_NAME}}',QUOTENAME(@TABLE_NAME));
DECLARE @SELECT_STR NVARCHAR(MAX) = REPLACE(N'SELECT * FROM [dbo].{{@TABLE_NAME}};',N'{{@TABLE_NAME}}',QUOTENAME(@TABLE_NAME));
DECLARE @DROP_STR NVARCHAR(MAX) = REPLACE(N'DROP TABLE [dbo].{{@TABLE_NAME}};' ,N'{{@TABLE_NAME}}',QUOTENAME(@TABLE_NAME));
-- Verify the output
SELECT @CREATE_TABLE_STR UNION ALL
SELECT @SELECT_STR UNION ALL
SELECT @DROP_STR
/* -- Execute
EXEC (@CREATE_TABLE_STR)
EXEC (@SELECT_STR)
EXEC (@DROP_STR)
--*/
Output
CREATE TABLE [dbo].[TTBL_1A064981187745488F5514D7497332A4]
(
[COL_01] INT
,[COL_02] INT
,[COL_03] INT
,[COL_04] INT
,[COL_05] INT
,[COL_06] VARCHAR(50)
,[COL_07] VARCHAR(150)
)
;
SELECT * FROM [dbo].[TTBL_1A064981187745488F5514D7497332A4];
DROP TABLE [dbo].[TTBL_1A064981187745488F5514D7497332A4];
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply