I like using temporary tables, but not exactly temporary ones. I mean, real tables, that are used temporarily. I have to apologise every time I mention this. But the advantage I find of using real tables in this way, is that the data persists, for investigation. For trouble-shooting or debugging. It is also useful for understanding the different stages of how you might be manipulating your data, which may be long and complicated.
Indeed, this method of using real tables temporarily enable me to modularize you data manipulation into manageable chunks. This helps me to understand what I am doing, for myself. And it also helps me to explain it to other people if necessary. It’s easier to hand over a piece of work from one person to another if the work can be visualized in small chunks.
I’m a great proponent of Modularized Data Manipulation. The reason for this is that I see a lot of systems that are extremely complex. Either they have started out this way, or they have grown organically to become this way. Some have been developed over years by different people, and they’ve each added their own different way coding. So there are many ways in which a system which transfers data from one place to another, with data manipulations, can become hard to understand. They can almost become black boxes. And small changes on them become expensive.
This is an issue that, I think, simplification using modularization can help. And temporary tables are a way to chunk our work. For example, to do this, I might call some data, and do a manipulation, and output that into a table. That’s one module of manipulation, one chunk of work. And then on that table, I’ll do another manipulation, and dump that out to another table, that’s another chunk. And I’ll continue this way, building up a series of manipulations, each with an output table of data. And I’ll do this until I’ve done everything I want to accomplish with this entire data manipulation.
It may be an output to a report. Or it may be a transaction into another system. But I will have accomplished it with a series of data in tables.
If I’m having trouble with a particular set of data, I can run this, and step through each block, one at a time. And by a process of elimination, find the place that is giving me the trouble. Then, within that block, I can go into the code to see why it is behaving the way it is.
If I want to do a controlled run of a particular set of data, I can do that too.

In T-SQL I would start with the deletion of a table that I plan to use, if it exists:

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRegions]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[tblRegions]
Go

Then I would either select into the table, or create a table and insert into it. Depending on the manipulation I am doing.

“Select Into” creates a new table. So I use the previous code, to check that the table doesn’t already exist. And if it does, then delete it, before running the following.
To select Into:

Select
P.PersonID
,P.Firstname
,P.Surname
,C.CountryName
Into
TempPeopleWorkingInCountries
From
tblPerson P
Inner Join tblWorkingInCountry WIC On P.PersonID = WIC.PersonID
Inner Join tblCountry C On WIC.CountryID = C.CountryID

When using “Create Table” also first check that there isn’t already a table with that name, using the code above. If it exists, delete it.
To Create a table, and then insert into it:

Create Table [dbo].[tblPerson](
[PersonID] [int] Null,
[Firstname] [nvarchar](50) Null,
[Surname] [nvarchar](50) Null
) On [Primary]

Create Table [dbo].[tblCountry](
[CountryID] [int] Null,
[CountryName] [nvarchar](50) Null
) On [Primary]

Create Table [dbo].[tblWorkingInCountry](
[WorkingInCountry] [int] Null,
[PersonID] [int] Null,
[CountryID] [int] Null
) On [Primary]

— tblPerson
Insert dbo.tblPerson (PersonID, Firstname, Surname) Values (1, ‘May’, ‘Day’)
Insert dbo.tblPerson (PersonID, Firstname, Surname) Values (2, ‘Frank’, ‘Holiday’)
Insert dbo.tblPerson (PersonID, Firstname, Surname) Values (3, ‘Chris’, ‘Newark’)

— tblCountry
Insert dbo.tblCountry (CountryID, CountryName) Values (1, ‘France’)
Insert dbo.tblCountry (CountryID, CountryName) Values (2, ‘Canada’)
Insert dbo.tblCountry (CountryID, CountryName) Values (3, ‘Germany’)
Insert dbo.tblCountry (CountryID, CountryName) Values (4, ‘Mexico’)

— tblWorkingInCountry
Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (1, 1, 2)
Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (2, 1, 4)
Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (3, 2, 1)
Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (4, 3, 2)
Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (5, 3, 3)
Insert dbo.tblWorkingInCountry (WorkingInCountry, PersonID, CountryID) Values (6, 3, 4)

… And there goes a modularized chunk of data manipulation.

Advertisements