Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Table Types
Published: Oct 09, 2016
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.