· 7 years ago · Nov 29, 2018, 06:16 PM
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
61--Demo set up
62DROP TABLE IF EXISTS [dbo].[Customers]
63CREATE TABLE [dbo].[Customers](
64 [CustomerID] [nchar](5) NOT NULL,
65 [CompanyName] [nvarchar](40) NOT NULL,
66 [ContactName] [nvarchar](30) NULL,
67 [ContactTitle] [nvarchar](30) NULL,
68 [Address] [nvarchar](60) NULL,
69 [City] [nvarchar](15) NULL,
70 [Region] [nvarchar](15) NULL,
71 [PostalCode] [nvarchar](10) NULL,
72 [Country] [nvarchar](15) NULL,
73 [Phone] [nvarchar](24) NULL,
74 [Fax] [nvarchar](24) NULL
75) ON [PRIMARY]
76GO
77
78DROP VIEW IF EXISTS CustomerView
79GO
80CREATE VIEW CustomerView
81AS
82SELECT *
83FROM dbo.Customers
84GO
85
86-------------------------------
87--The solution
88SET NOCOUNT ON;
89
90DECLARE @sql NVARCHAR(MAX)
91 ,@cols NVARCHAR(MAX) = N'';
92
93SELECT @cols += N',' + NAME + ' ' + system_type_name
94FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.CustomerView', NULL, 1);
95
96SET @cols = STUFF(@cols, 1, 1, N'');
97SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'
98SET @sql = replace(@sql,',',',' + char(10))
99print @sql
100
101CREATE TABLE #tmp(CustomerID nchar(5),
102CompanyName nvarchar(40),
103ContactName nvarchar(30),
104ContactTitle nvarchar(30),
105Address nvarchar(60),
106City nvarchar(15),
107Region nvarchar(15),
108PostalCode nvarchar(10),
109Country nvarchar(15),
110Phone nvarchar(24),
111Fax nvarchar(24));
112
113SELECT TOP 0 *
114INTO #SomeNewTable
115FROM [SomeDB].[SomeSchema].[SomeView]