How to update a table from another table? 1

[timoteo]

I am using VB 6.0 (SP5) to access a SQL Server 2000 database. I have a tempory table that stores and displays the data until the user confirms the update. How can I move this temparary data to the permenent table?

Thanks in advance for any suggestions.

Timoteo

 

[MarkSweetland]

UPDATE
tbl_main
SET
col1 = tbl_temp.col1,
col2 = tbl_temp.col2,
col3 = tbl_temp.col3,
col4 = tbl_temp.col4,
col5 = tbl_temp.col5
FROM
tbl_temp
WHERE
tbl_main.pri_key_col = tbl_temp.pri_key_col


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[timoteo]

MarkSweetland,

Thanks for the help. However, I must apologize because I wasn't clear in my original question.

What I am actually trying to do is *insert* the new records a the end of the permenent table. I know how to insert predefined values into a table using the INSERT INTO command followed by the name of the table, column names, the VALUES command, and finally the values I want to insert. But I can't think of a way to take values from one table and insert them at the end of another table.

Sorry for the confusion.

Timoteo

 

[MarkSweetland]

First, the SQL statement:

INSERT INTO
tbl_main
SELECT
col1, col2, col3,....
FROM
tbl_temp


Secondly, if you are trying to insert into a table with an identity column defined, you'll have to either ensure the identity column is the last column defined in the table or list the non-identity column names in the INSERT clause.

INSERT INTO
tbl_main (col1, col2, col3,...) /* list all but identity */
SELECT
col1, col2, col3,.... /* list all but identity */
FROM
tbl_temp


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[timoteo]

MarkSweetland,

Thanks for the tip, you were right on. It works perfect!

Timoteo