I have a table (a_temp) that holds rows that are yet to be activated.

I need to insert the row from a_temp to a, then delete the row from a_temp,
based on the a_temp.id colum.

What would be the best way to do this?

Thanks

Please provide DDL (CREATE TABLE), example data (INSERT INTO) and your
expected result so we know what you are trying to do and so we can help you
better.

Michael MacGregor
Database Architect
SalesDriver

INSERT a
SELECT * FROM a_temp
[WHERE <any conditions>]

DELETE FROM a_temp
WHERE EXISTS (SELECT * FROM a
            WHERE a.id = a_temp.id)

If there is no filters(WHERE) in the first INSERT you can
just truncate the a_temp table.

--
- Anith

INSERT INTO a (M_STATUS,M_NAME,

M_USERNAME,M_PASSWORD, M_EMAIL, M_COUNTRY, M_HOMEPAGE,

M_SIG, M_DEFAULT_VIEW,  M_LEVEL, M_AIM, M_YAHOO, M_ICQ,

M_POSTS, M_DATE, M_LASTPOSTDATE, M_LASTHEREDATE,

M_TITLE, M_SUBSCRIPTION, M_HIDE_EMAIL, M_RECEIVE_EMAIL,

M_LAST_IP, M_IP, M_FIRSTNAME, M_LASTNAME,

M_OCCUPATION, M_SEX, M_AGE, M_HOBBIES, M_LNEWS,

M_QUOTE, M_BIO, M_MARSTATUS, M_LINK1, M_LINK2, M_CITY,

M_PHOTO_URL, M_STATE )

thanks

Not much point providing only half of an INSERT INTO statement and no DDL at
all. Care to try again.

DDL means:
CREATE TABLE MyTable
(MyPK INT PRIMARY KEY,
MyDataCol1   <datatype>,
etc....

Example date means:
INSERT INTO MyTable (MyDateCol1,..etc..)
VALUES (<dataforcol1>, etc....)                --> very important bit as
it's the actual data

Other than that, did Anith's generic example not help you?

Michael MacGregor
Database Architect
SalesDriver

Sorry I wasn't quite clear on this.

In table a there is a member_id column which is the key field,
autoincrement.

table a_temp columns are a duplicate of the table a columns, except
the
member_id field is called temp_id. There is no member_id column in the
a_temp table.

this code:
INSERT INTO a (M_NAME, M_PASSWORD, M_EMAIL, M_HIDE_EMAIL, M_DATE,
M_COUNTRY, M_SIG, M_YAHOO, M_ICQ, M_AIM, M_POSTS, M_HOMEPAGE,
M_LASTHEREDATE, M_STATUS, M_LAST_IP, M_IP, M_FIRSTNAME, M_LASTNAME,
M_CITY,
M_STATE, M_PHOTO_URL, M_LINK1, M_LINK2, M_AGE, M_MARSTATUS, M_SEX,
M_OCCUPATION, M_BIO, M_HOBBIES, M_LNEWS, M_QUOTE)

SELECT (M_NAME, M_PASSWORD, M_EMAIL, M_HIDE_EMAIL, M_DATE, M_COUNTRY,
M_SIG, M_YAHOO, M_ICQ, M_AIM, M_POSTS, M_HOMEPAGE,  M_LASTHEREDATE,
M_STATUS, M_LAST_IP, M_IP, M_FIRSTNAME, M_LASTNAME, M_CITY, M_STATE,
M_PHOTO_URL, M_LINK1, M_LINK2, M_AGE,  M_MARSTATUS, M_SEX,
M_OCCUPATION,
M_BIO, M_HOBBIES, M_LNEWS, M_QUOTE)

FROM         a_temp
WHERE     TEMP_ID = '2'

generates the error [Microsoft][ODBC SQL Server Driver][SQL
Server]Line 1"
Incorrect syntax near ','.

Sorry for the delay. My ISP(ameritech, not to name names) only
supports newsgroup messaging during certain phases of the moon)

Thanks

Well for a start get rid of the brackets around the columns in the SELECT,
other than that, it's still hard to determine because you are only providing
part of the requested information - no DDL, no data.

Michael MacGregor
Database Architect
SalesDriver