How to create a table using column names present in another table?-CodePudding

I am working on SQL Server 2016.
I want to know is there anyway I can create a permanent or temporary table with column names present in another table?
See the table: MAINTAGS and its code below. I want DOB as my first column, POB as 2nd and so on...
Currently there are 12 names in my MAINTAGS table, so new table will have total 12 columns.

More names can be added as well in MAINTAGS

ID_MAINTAGS NAMES_MAINTAGS
============== ==============
1 DOB
2 POB
3 citizen
4 nationality
5 Additional Sanctions Information
6 Passport
7 National ID No.
8 Email Address
9 Gender
10 a.k.a.
11 Linked To:
12 Phone Number

IF OBJECT_ID('dbo.MAINTAGS', 'U') IS NOT NULL 
  DROP TABLE dbo.MAINTAGS; 

CREATE TABLE MAINTAGS(ID_MAINTAGS INT IDENTITY(1,1), NAMES_MAINTAGS VARCHAR(MAX));

INSERT INTO MAINTAGS (NAMES_MAINTAGS)
VALUES
('DOB'),('POB'),('citizen'),('nationality'),
('Additional Sanctions Information'), ('Passport'),('National ID No.'),
('Email Address'),('Gender'),('a.k.a.'),('Linked To: ')

CodePudding user response：

Let me start by stating that this smells strongly of an XY Problem and the fact you want to do this very strongly suggests a design flaw.

I'm also going to assume that you have something that defines the data type; I'm going for some columns with the data type, length, precision and scale properties. If you don't have this, add them.

Anyway, you can achieve this with some simple string aggregation, and then execute the dynamic statement:

DECLARE @SchemaName sysname = N'dbo',
        @TableName sysname = N'NewTableName';

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

DECLARE @Delimiter = N','   @CRLF;

SELECT @SQL = N'CREATE TABLE '   QUOTENAME(@SchemaName)   N'.'   QUOTENAME(@TableName)   N'('   @CRLF  
              STRING_AGG(N'    '   QUOTENAME(YT.ColumnName)   N' '   QUOTENAME(YT.DataType)   
              CASE WHEN YT.Length IS NOT NULL OR YT.Precision IS NOT NULL OR YT.Scale IS NOT NULL THEN N' ('   CONCAT_WS(',',YT.Length,YT.Precision,YT.Scale)   N')' ELSE N'' END,@Delim)
FROM dbo.YourTable YT;

--PRINT @SQL --Your best friend.
EXEC sys.sp_executesql @SQL;

Note that in your example the column NAMES_MAINTAGS allows up to ~2 billion characters; object names cannot be this long. As a result these values will be truncated to 128 characters; if you therefore have duplicate names due to this, the CREATE statement will fail.

CodePudding user response：

Select Top 0 * into NewTable from OldTable