This was something which I used recently within my own personal work at home because I was writing a stored procedure which involved regularly creating tables with the same structure.
In the end I got annoyed with copy and paste and decided to use a Table Type as a template.
As the last line implies, a Table Type can be thought of as a table template. You can define the structure of the type and from then on you can re-use that at will.
i’m going to go straight into code with this one and create my table type which I’ll be calling “demoType”.
You simply have to create a new Type and tell SQL Server that it’s a table. Then proceed to your declaration:
if exists
(
select name
from sys.table_types
where name = 'demoType'
)
begin
drop type demoType
end
create type demoType as table
(
id int identity,
myDate smalldatetime,
myValue smallint,
misc varchar(10)
)
go
That’s literally it. It’s that simple.
Now you can just declare a new table using this template and fill it with data without having to keep copying and pasting column lists:
declare @newTable demoType
insert into @newTable
select current_timestamp, 1, 'a'
select *
from @newTable
It’s that simple.
The other advantage is that if you’re using these throughout your code then if you suddenly find that you needed a column to be a varchar(50) instead of a varchar(45) then you need to make just one change to your Type rather than a lengthy find and replace.