[sql] Inserting data into a temporary table

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?

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

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

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.

SELECT * 
INTO #TempTable
FROM table

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

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

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;

insert #temptable
select idfield, datefield, namefield from yourrealtable

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

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

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;

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

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