Geek asks and answers

Create a table from the rows of another table along with its properties

I have a table named Table1

Column Name Data type Max length Precision Scale is_nullable Primary Key
    Price float 8 53 0 1 0
    Name varchar 180 0 0 1 0
    Id_no int 4 10 0 1 1
    DOB date 3 10 0 1 0

I need to create another table named Table2where the column rows of a column of table 1 (along with properties such as Data Type, Maximum Length, etc.) should be my columns of another table, for example

Table 2
Price Name Id_No DOB

(Note that when creating a table query, properties must also be considered)

+5
source share
3 answers

You can create a copy of the table with select into:

select  *
into    NewTable
from    OldTable
0
source 
create table table2
as
select * form table1;
0
source

You can dynamically create an SQL statement and execute this command.

DECLARE @dsql nvarchar(max) = N''

SELECT @dsql += QUOTENAME([Column Name]) + ' ' + 
       [Data type] + 
        + CASE WHEN [Data type] = 'varchar' 
               THEN '(' + CAST([Max length] AS nvarchar(4)) + ')' ELSE '' END + ','        
FROM Table1

SELECT @dsql = 'CREATE TABLE Table2 (' + LEFT(@dsql, LEN(@dsql) - 1) + ')'
EXEC sp_executesql @dsql
0
source

More articles:


All Articles