Tmp Tables (copying from..) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Tmp Tables (copying from..)

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-04-02 : 07:32:55

Hi
I need to update an existing table with the contents of a temporary table ?
what is the syntax for this, for example i have temp table #tmptable

I need to add this to a existing table (ExTable)
Can i do something like
INSERT into Extable..
.
. (data)
FROM SELECT * from #tmptable

??
any help appreciated ..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-02 : 07:35:12

UPDATE t
SET t.Col1 = x.ColA
FROM Table1 AS t
INNER JOIN Table2 AS x ON x.Col99 = t.ColGreen

Peter Larsson
Helsingborg, Sweden

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-04-02 : 07:40:14

So in this case x is the tmp table ?

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-04-02 : 07:40:35

I depends if you are just inserting the infomation from the temp table then yes that you work for example

insert into ExTable (col1, col2,col3)
select col1,col2,col3
from #TempTable

--------------------------
You only learn by practicing

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-04-02 : 08:03:44

insert into ExTable (col1, col2,col3)
select col1,col2,col3
from #TempTable

So will this work even though the SELECT is AFTER the insert ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-02 : 08:07:31

Yes it does work. You can refer to Books Online for more information on the syntax.

quote:

Inserting Rows Using INSERT...SELECT
The SELECT subquery in the INSERT statement can be used to add values into a table from one or more other tables or views. Using a SELECT subquery also lets more than one row be inserted at one time.

This INSERT statement inserts into a separate table some of the data from all the rows in titles whose type is modern cooking:

USE pubs
INSERT INTO MyBooks
SELECT title_id, title, type
FROM titles
WHERE type = 'mod_cook'

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-04-02 : 09:13:20

Yes it will, it's just the same really as doing the following which will just insert one line, using the select statement is a quick way of populating a table with data that is already avaliable in another table.

insert into Table (col1,col2,col3)
values ('col1','col2','col3)

as long as the select does not have an indiffrent amount of column to the insert into you will be ok otherwise an error will returned.

--------------------------
You only learn by practicing

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-04-02 : 09:29:34

so i have
INSERT INTO TABLE(user_code,user_fullname,user_name)
SELECT user_code,user_fullname,user_name
FROM #TMP_Table
----
is this correct syntax ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-02 : 09:35:10

quote:
Originally posted by pazzy11

so i have
INSERT INTO TABLE(user_code,user_fullname,user_name)
SELECT user_code,user_fullname,user_name
FROM #TMP_Table
----
is this correct syntax ?

Why don't you give it a try. Is there any error messages ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-02 : 09:42:47

quote:
Originally posted by pazzy11

so i have
INSERT INTO TABLE(user_code,user_fullname,user_name)
SELECT user_code,user_fullname,user_name
FROM #TMP_Table
----
is this correct syntax ?

No it is not.

INSERT INTO TABLE <table-name>(user_code,user_fullname,user_name)
SELECT user_code,user_fullname,user_name
FROM #TMP_Table

Replace <table-name> with the name of table in which you want to insert rows.

Note: Above statement will work only when the data type and size of columns in SELECT query and columns in destination table match.
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-04-02 : 09:44:15

yes thats what i did but still it wont work

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-02 : 09:47:44

quote:
Originally posted by pazzy11

yes thats what i did but still it wont work

Do you get any error message ?

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-04-02 : 09:56:13

yes it states that the column names don't exist.. which actually could be true.. i have been assuming the tables are the same...
but how can i check a tmp table!!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-02 : 09:59:43

"how can i check a tmp table?"

Huh?

Check the CREATE TABLE statement which you are using to create the temp table or if it is SELECT...INTO, then check the column names you are selecting !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-02 : 09:59:54

How do you create the temp table ?

using
1. create table #temp_table . . . or
2. insert into #temp_table ?

if 1. check the DDL
if 2. do a select * from #temp_table after you have insert into ..

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-04-02 : 10:02:53

Yes that’s the correct syntax it should be something like this.

insert into employees (firstname, Lastname,age)
select firstname, lastname, age
from #tempemployees

If that's similar to what you have, then I can't how an error is occurring unless you have no data in the temp table

Post the syntax you are using and then the error message that you are getting back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources