Very early into my SQL career I had to get comfortable moving records around between tables and databases. There are several ways to do it, you just have to get used to some of the requirements in moving the records.

Moving records into a temporary table

The first time you move records, you’ll probably need to “make a copy” of some records so you can do some look-up, or experimentation on them. To do that, you can use SELECT INTO.

SELECT
Column1, Column2, Column3...
INTO TemporaryTable
FROM SourceTable

You could also do this same operation, but rather than using a normal table, you could create a temp table in your tempdb.

SELECT
Column1, Column2, Column3...
INTO #TemporaryTable
FROM SourceTable

There are a few differences when creating temp tables in your tempdb. First, you need to know there are two types of temp tables, local and global.

All Temp Tables

You cannot set up partitioning on a temp table.
They are dropped when the code that creates them goes out of scope (you close the session, or the stored procedure finishes).
You can’t define foreign key constraints on a temp table.

Local Temp Tables

Only visible in the current session, so you can’t share the data with other sessions.
Table names must start with the # symbol, and can use no more than 116 characters in their name.
If you create more than one in a single stored procedure, they all have to have uniqiue names, just like regular tables.

Global Temp Tables

Table names must start with ##.
These temp tables CAN be shared between sessions.

The only time I’ll use a temp table is when I know I’m going to be dealing with a lot of records and I’ll need to index the table. If I just need to move records from one place to another, and I’ll keep the table in scope the full time, then I use table variables instead.

Using Table Variables

When you use a table variable, you have to set up the table before you can insert data. You can do an INSERT INTO statement. If I were to repeat the copy from above, but use a table variable instead I would do something like the following.

CREATE TABLE @TemporaryTable (
Column1 VARCHAR(10)
, Column2 VARCHAR(10)
, Column3 VARCHAR(10) )

INSERT INTO @TemporaryTable
SELECT
Column1, Column2, Column3
FROM SourceTable

I would then have access to that temp table until I close my session.

Summary

With these four options you can move data all around your database. You need to get used to these four options, and more importantly their limitations. When you’re comfortable with that, there’s very little data manipulation you won’t be able to accomplish!

If you have any questions about this material, please let me know! I’m here to help.

Moving Data Between Tables