How do I generate table structure from a view, in one script? Will only need data types, I am seeking a Table Create Script with some type of dynamic sql
(1) can remove primary key/constraint,
(2) don't care if null/not null added (would be nice to have however)
I have proposed sample question/answer below, feel free to code review, edit/optimize for other data types not included in table in future, special cases/issues/exceptions may have not accounted for. Is there any open source code for this by the way?
Table 1 and 2:
create table dbo.Customer
(
CustomerId int primary key,
CustomerName varchar(255),
ZipCode varchar(9)
)
create table dbo.CustomerTransaction
(
CustomerTransactionId int primary key identity(1,1),
CustomerId int,
SalesAmount numeric (10,2),
PurchaseDate datetime
)
View:
create view dbo.CustomerTransactionVw
as
select
ct.CustomerTransactionId,
ct.SalesAmount,
ct.PurchaseDate,
cust.CustomerId,
CustomerName,
cust.ZipCode
from dbo.CustomerTransaction ct
inner join dbo.Customer cust
on cust.CustomerId = ct.CustomerId
Intended Table Create Script:
create table dbo.CustomerTransactionBigTable
(
CustomerTransactionId int identity(1,1),
CustomerId int,
SalesAmount numeric (10,2),
PurchaseDate datetime,
CustomerId int,
CustomerName varchar(255),
ZipCode varchar(9)
)
Current Proposed Solution:
declare @TableCode varchar(max) = 'create table dbo.CustomerLargeTable
( ' +
(select STUFF((
SELECT ',
'
+ c.name + ' ' +
case
when t.name like '%char%' then t.name + '(' + cast(c.max_length as varchar(10)) + ')'
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)) + ')'
else t.name
end
FROM .sys.columns c
inner JOIN sys.types t
on t.user_type_id = c.user_type_id
and t.system_type_id = c.system_type_id
where c.object_id = object_id('CustomerTransactionVw') and is_identity = 0
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))
+ '
)'
print @TableCode
Note: I am only interested in table structure DDL, not data DML
SELECT ... INTO dbo.TableName FROM...
syntax. SQL Server will you can use CAST or CONVERT to set the data types or simply let SQL Server use the column type of the original tables. – Jason A. Long Nov 27 '18 at 9:36