QEzap7AR

1create table dbo.Customer
2(
3     CustomerId int primary key,
4     CustomerName varchar(255),
5     ZipCode varchar(9)
6)
7create table dbo.CustomerTransaction
8(
9     CustomerTransactionId int primary key identity(1,1),
10     CustomerId int,
11     SalesAmount numeric (10,2),
12     PurchaseDate datetime
13)
14	
15create view dbo.CustomerTransactionVw
16as
17select 
18     ct.CustomerTransactionId,
19     ct.SalesAmount,
20     ct.PurchaseDate,
21     cust.CustomerId,
22     CustomerName,
23     cust.ZipCode
24from dbo.CustomerTransaction ct
25inner join dbo.Customer cust
26  on cust.CustomerId = ct.CustomerId
27	
28create table dbo.CustomerTransactionBigTable
29(
30     CustomerTransactionId int identity(1,1),
31     CustomerId int,
32     SalesAmount numeric (10,2),
33     PurchaseDate datetime,
34     CustomerId int,
35     CustomerName varchar(255),
36     ZipCode varchar(9)
37)
38	
39declare @TableCode varchar(max) = 'create table dbo.CustomerLargeTable 
40( ' + 
41    (select STUFF((
42    SELECT ', 
43    '    
44    + c.name + ' ' + 
45case 
46    when t.name like '%char%' then t.name + '(' + cast(c.max_length as varchar(10)) + ')' 
47    when t.name like '%numeric%' or t.name like '%decimal%' then t.name + '(' + cast(c.precision as varchar(10)) + ',' + cast(c.scale as varchar(10)) + ')'
48    else t.name
49end
50FROM .sys.columns c 
51inner JOIN sys.types t
52    on t.user_type_id = c.user_type_id
53    and t.system_type_id = c.system_type_id
54where c.object_id = object_id('CustomerTransactionVw') and is_identity = 0
55FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))
56+ '
57)'  
58
59print @TableCode
60	
61SELECT TOP 0 *
62INTO #SomeNewTable
63FROM [SomeDB].[SomeSchema].[SomeView]
64	
65--Demo set up
66DROP TABLE IF EXISTS [dbo].[Customers]
67CREATE TABLE [dbo].[Customers](
68    [CustomerID] [nchar](5) NOT NULL,
69    [CompanyName] [nvarchar](40) NOT NULL,
70    [ContactName] [nvarchar](30) NULL,
71    [ContactTitle] [nvarchar](30) NULL,
72    [Address] [nvarchar](60) NULL,
73    [City] [nvarchar](15) NULL,
74    [Region] [nvarchar](15) NULL,
75    [PostalCode] [nvarchar](10) NULL,
76    [Country] [nvarchar](15) NULL,
77    [Phone] [nvarchar](24) NULL,
78    [Fax] [nvarchar](24) NULL
79) ON [PRIMARY]
80GO
81
82DROP VIEW IF EXISTS CustomerView 
83GO
84CREATE VIEW CustomerView
85AS
86SELECT *
87FROM dbo.Customers
88GO
89
90-------------------------------
91--The solution    
92SET NOCOUNT ON;
93
94DECLARE @sql NVARCHAR(MAX)
95    ,@cols NVARCHAR(MAX) = N'';
96
97SELECT @cols += N',' + NAME + ' ' + system_type_name
98FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.CustomerView', NULL, 1);
99
100SET @cols = STUFF(@cols, 1, 1, N'');
101SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'
102
103print @sql
104	
105CREATE TABLE #tmp(CustomerID nchar(5),CompanyName nvarchar(40),ContactName nvarchar(30),ContactTitle nvarchar(30),Address nvarchar(60),City nvarchar(15),Region nvarchar(15),PostalCode nvarchar(10),Country nvarchar(15),Phone nvarchar(24),Fax nvarchar(24));