How to insert into a table from temp table?

Question

I already have values in a temp table and I want to insert it into my table.

I follow this syntax

IF NOT EXISTS (SELECT 1 FROM ABC abc JOIN #Temp t ON abc.ID = t.ID)

insert into MyTable(Id,Name)
select values (t.ID, t.Name)
From t

I have just the name t as an alias I created in a condition before this insert.

Is this correct? Some people use @ etc. I am confused.

possible duplicate of [Insert into table from temporary table](http://stackoverflow.com/questions/27445247/insert-into-table-from-temporary-table) — Tanner, Sep 02 '15 at 10:34

Lukasz Szozda · Accepted Answer · 2015-09-02T10:54:52.367

7

Correct syntax:

insert into MyTable(Id,Name)
select t.ID, t.Name
From #temp t

Always read manual

edited Sep 02 '15 at 10:54

answered Sep 02 '15 at 10:24

Lukasz Szozda

162,964
23
234
275

Thanks, I always see that some people who use @, they use @ in first field and rest all just field name and in your query, you use t in last field and not in first field? Confused.. – Jasmine Sep 02 '15 at 10:27
Everyone's answer is different here :( I am confused – Jasmine Sep 02 '15 at 10:31
@Deevinee You can't use alias defined elsewhere, Alias always work in one current instruction, so you need `insert into MyTable(Id,Name) select ID, t.Name From #temp t` – Lukasz Szozda Sep 02 '15 at 10:34
Thank you, and my question about t in Name and not in ID ? – Jasmine Sep 02 '15 at 10:39
@Deevinee You want ID from parameter? Like `insert into MyTable(Id,Name) select @ID, t.Name From #temp t ` – Lukasz Szozda Sep 02 '15 at 10:41
No, just confused why you didn't use t.ID? and just ID but in 2nd field you use t.Name and not just name... – Jasmine Sep 02 '15 at 10:53
@Deevinee If you have one table and no ambiguous name you can skip alias part. – Lukasz Szozda Sep 02 '15 at 10:54
Thank you :) I am leaving home. Its 9 PM. I came at 8 AM :( Damn !!! Haha somehow got two SPs down :) Thanks so much for your help, see you tomorrow :) Have a good day.... – Jasmine Sep 02 '15 at 10:57

score 0 · Answer 2 · answered Sep 02 '15 at 10:27

0

You can check this

insert into Table1(Id,Name) 
select ID,Name
From t

answered Sep 02 '15 at 10:27

Urvi

220
1
3
11

score 0 · Answer 3 · edited Oct 06 '22 at 13:09

0

Insert into table_name(column_name1,column_name2,column_name3)
(select temp_table_col1_1,temp_table_col1_1,temp_table_col1_1 
 from temp_table_name
 <where condition if needed>)

edited Oct 06 '22 at 13:09

Syscall

19,327
10
37
52

answered Sep 02 '15 at 10:30

Binitta Mary

134
3

score 0 · Answer 4 · answered Sep 02 '15 at 10:30

0

Try this

INSERT INTO Table1(Id, Name)
SELECT t.ID, t.Name FROM t

answered Sep 02 '15 at 10:30

Ajay

6,418
18
79
130

score 0 · Answer 5 · edited Apr 13 '17 at 12:42

Please try with below code snippet.

IF NOT EXISTS (SELECT 1 FROM ABC abc JOIN #Temp t ON abc.ID = t.ID)

    insert into MyTable(Id,Name) select t.ID, t.Name From #Temp t

Temp Table

CREATE TABLE #Temp1 ( Name VARCHAR(MAX) )

Global Temp Table

CREATE TABLE ##Temp1 ( Name VARCHAR(MAX) )

Table Variable

DECLARE @Temp1 TABLE( Name VARCHAR(MAX) )

In SQL Server, for temp table we used '#' and for table variable we used '@'. For more detailed information about temp table and table variable please look into this.

score 0 · Answer 6 · edited Oct 06 '22 at 13:09

0

try this:

insert into MyTable(Id,Name)
(select t,Name From t)

edited Oct 06 '22 at 13:09

Syscall

19,327
10
37
52

answered Oct 06 '22 at 12:22

KAWTAR ELKASDIR

1

How to insert into a table from temp table?

6 Answers6