Newbie help Please - best way to copy a row 
Author Message
 Newbie help Please - best way to copy a row
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



Mon, 21 Feb 2005 02:20:05 GMT
 Newbie help Please - best way to copy a row

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



Mon, 21 Feb 2005 02:24:23 GMT
 Newbie help Please - best way to copy a row
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



Mon, 21 Feb 2005 02:29:53 GMT
 Newbie help Please - best way to copy a row
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


Quote:
> 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



Mon, 21 Feb 2005 04:24:06 GMT
 Newbie help Please - best way to copy a row
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



Mon, 21 Feb 2005 20:02:34 GMT
 Newbie help Please - best way to copy a row
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

Quote:

> 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



Tue, 22 Feb 2005 00:04:52 GMT
 Newbie help Please - best way to copy a row
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



Tue, 22 Feb 2005 01:56:30 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Please give opinions on different ways to setup disks for best performance under SQL

2. Newbie Help lease - copy row

3. newbie - simple row copy

4. newbie, please, please help!!

5. Newbie Needs Help Please Please

6. Newbie, please, please help

7. Newbie, please, please help!!

8. newbie import/export basic problem, please help please

9. Newbie, please, please help!!

10. Ways to automate copying data into SQL Server 6.5

11. Please suggest some good site for newbie for DB2

12. The Best Book for a Newbie Please!!!!!!!!!


 
Powered by phpBB® Forum Software