Inserting data into a temporary table

Sql Problem Overview

After having created a temporary table and declaring the data types like so;

CREATE TABLE #TempTable(
ID int,
Date datetime,
Name char(20))

How do I then insert the relevant data which is already held on a physical table within the database?

Sql Solutions

Solution 1 - Sql

INSERT INTO #TempTable (ID, Date, Name) 
SELECT id, date, name 
FROM physical_table

Solution 2 - Sql

To insert all data from all columns, just use this:

SELECT * INTO #TempTable
FROM OriginalTable

Don't forget to DROP the temporary table after you have finished with it and before you try creating it again:

DROP TABLE #TempTable

Solution 3 - Sql

SELECT  ID , Date , Name into #temp from [TableName]

Solution 4 - Sql

My way of Insert in SQL Server. Also I usually check if a temporary table exists.

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP Table #MyTable

SELECT b.Val as 'bVals'
  INTO #MyTable
FROM OtherTable as b

Solution 5 - Sql

SELECT * 
INTO #TempTable
FROM table

Solution 6 - Sql

I have provided two approaches to solve the same issue,

> Solution 1: This approach includes 2 steps, first create a temporary table with > specified data type, next insert the value from the existing data > table.

CREATE TABLE #TempStudent(tempID  int, tempName  varchar(MAX) )
INSERT INTO #TempStudent(tempID, tempName) SELECT id, studName FROM students where id =1

SELECT * FROM #TempStudent

> Solution 2: This approach is simple, where you can directly insert the values to > temporary table, where automatically the system take care of creating > the temp table with the same data type of original table.

SELECT id, studName  INTO #TempStudent FROM students where id =1

SELECT * FROM #TempStudent

Solution 7 - Sql

After you create the temp table you would just do a normal INSERT INTO () SELECT FROM

INSERT INTO #TempTable (id, Date, Name)
SELECT t.id, t.Date, t.Name
FROM yourTable t

Solution 8 - Sql

The right query:

drop table #tmp_table

select new_acc_no, count(new_acc_no) as count1
into #tmp_table
from table
where unit_id = '0007' 
group by unit_id, new_acc_no
having count(new_acc_no) > 1

Solution 9 - Sql

insert into #temptable (col1, col2, col3)
select col1, col2, col3 from othertable

Note that this is considered poor practice:

insert into #temptable 
select col1, col2, col3 from othertable

If the definition of the temp table were to change, the code could fail at runtime.

Solution 10 - Sql

Basic operation of Temporary table is given below, modify and use as per your requirements,

-- CREATE A TEMP TABLE

CREATE TABLE #MyTempEmployeeTable(tempUserID  varchar(MAX), tempUserName  varchar(MAX) )

-- INSERT VALUE INTO A TEMP TABLE

INSERT INTO #MyTempEmployeeTable(tempUserID,tempUserName) SELECT userid,username FROM users where userid =21

-- QUERY A TEMP TABLE [This will work only in same session/Instance, not in other user session instance]

SELECT * FROM #MyTempEmployeeTable

-- DELETE VALUE IN TEMP TABLE

DELETE FROM #MyTempEmployeeTable

-- DROP A TEMP TABLE

DROP TABLE #MyTempEmployeeTable

Solution 11 - Sql

INSERT INTO #TempTable(ID, Date, Name)
SELECT OtherID, OtherDate, OtherName FROM PhysicalTable

Solution 12 - Sql

insert #temptable
select idfield, datefield, namefield from yourrealtable

Solution 13 - Sql

All the above mentioned answers will almost fullfill the purpose. However, You need to drop the temp table after all the operation on it. You can follow-

INSERT INTO #TempTable (ID, Date, Name) 
SELECT id, date, name 
FROM physical_table;

IF OBJECT_ID('tempdb.dbo.#TempTable') IS NOT NULL 
	DROP TABLE #TempTable;

Solution 14 - Sql