duplicating\copying rows

Include only those fields that u want to duplicate.

INSERT INTO tableA (col1, col2, ..... col7)
VALUES (rs.val1, rs.val2, ... rs.val7)

INSERT INTO Table1 (Field4, Field5, Field6, Field7, Field8, Field9, Field10)
SELECT Field4, Field5, Field6, Field7, Field8, Field9, Field10
FROM Table1
WHERE Field1 = 1

The assumption here is that you have default values for Field1, Field2 and Field3.

Cheers, Andrew

TextReport suggested a very valid pre-req - The assumption here is that you have default values for Field1, Field2 and Field3.

To add to it, these fields may either be NULL or they may be NOT NULL and should have a default value.

just a minute...i forgot to say something...
al the duplication going on in the same table...

for example i got row with id=5
now i want to create row where id will be 6
and to copy 7 fields...

and i do not want to use only one query-with no rs

I made the assumption that that rowid is an identity row and will increment itself.

The code below will add one to the ID field if youre not using a identity column.
Cheers, Andrew

INSERT INTO Table1 (ID, Field5, Field6, Field7, Field8, Field9, Field10)
SELECT ID+1, Field5, Field6, Field7, Field8, Field9, Field10
FROM Table1
WHERE ID = 5

Select allOpen in new window

i just can use ID and ID+1 instead of rs("id")?:)

i think i was right and there is not such option...but i am not sure...that is the error i get:
[MySQL][ODBC 3.51 Driver][mysqld-4.1.21-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ש','××S ×¢×£',DATE_ADD(LOCALTIME(),INTERVAL 120 minute),'12675645-1633' at line 1

Can you show the code you are running top get this error.
Cheers, Andrew

yes...as soon as i get back to that computer i will post it...
but you sure in your suggestion syntax?
i mean have you tested it before?

thank you once again:)

Yes pretty much bog standard SQL, however, normally the ID field is an auto increment so you would not specify the field in either the fields list of teh SELECT.
Cheers, Andrew

mysql="INSERT INTO Table1 (ID, Field5, Field6, Field7, Field8, Field9, Field10) SELECT (SELECT MAX(ID) FROM Table1) + 1, Field5, Field6, Field7, Field8, Field9, Field10 FROM Table1 WHERE ID = 5"
isn'  there a ) missing?

why id=5?
i never know what will be the id of the new record...

>> isn'  there a ) missing?

Nope.

>> why id=5?

If not this, then what is the filter condition that determines which records to be duplicated?
Or do u want to duplicate them all?

sorry but i will tell you what i do not get here:
usually the syntax is:
mySQL="INSERT INTO table(columns names) VALUES (values that wil be unserted...)

your query got no" VALUES"

We cannot use the VALUES keyword when the INSERT command gets its values from an existing table.

1. INSERTing data from pre-defined values -
INSERT INTO table (id, name) VALUES (1, 'ABC');

2. INSERTing data from a table -
INSERT INTO table (id, name) SELECT id, name FROM table2;

here is my original query after the you help:
mysql="INSERT INTO demo (newid,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) SELECT (SELECT MAX(id) FROM demo) + 1, '" &starttask& "','" &firstread& "','" &lastread& "','" &uname& "','" &tasks& "','" &ptime& "','" &sub& "','" &fromloc& "','" &toloc& "','" &customer& "','" &hlimit& "','" &carnum& "',1  FROM demo WHERE id=" &pid

and there is a syntax error going on there

you right...i was using pre definded vars...

ok...but there is a number that i have to insert...1 to status
mysql="INSERT INTO demo (newid,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) SELECT (SELECT MAX(ID) FROM demo) + 1,,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,1  FROM demo WHERE id=" &pid


what is wrong with it?

besides the double comma

mysql="INSERT INTO demo (newid,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,status) SELECT (SELECT MAX(ID) FROM demo) + 1,starttask,firstread,lastread,uname,tasks,ptime,sub,fromloc,toloc,customer,hlimit,carnum,1  FROM demo WHERE id=" &pid

i don't why, but when i use your code, the new record that have to be opened insert all the data to the next row...
that mean if whe now on row id=5 and there is 50 rows...then when we will use this query, there won;t be opened 51 row...instead of it the row with id 6 will receve the changes...

that is not good cause the id's could be deleted...

sasha85, I have said it in your other qustion and I will repeat it here as it is a fundamental issue and is causing all your problems here.

"I will raise this again it is very bad design to control the ID the way you are trying to do this. use the Auto Increment feature for the ID field."

Cheers, Andrew

i am using auto inc..."newid" is like a serial number

there is "id" and "newid"

ALL i want is to copy some records fields into new record and place the new record last...as it usually being added.
why that is so big problem?

@sasha85: Can u just show us wat does this SQL fetch - SELECT MAX (id) FROM Table1

i will explain:
when you open a new recored you don'tknow what auto id it will recieve cause may be some one else in the same moment opened a record too...

i need that in the id field(key) and in the newid field will be the same number...but because i can't know the number that the id field will recieve, i am telling the the newid field to be the maximum number of id+1

If u want them both to be the same, why don't u make them both as Auto-Inc ?

You can only have 1 autoIncrement field in a table but I don't understand why you would have 2 ID fields 1 auto Invrement and 1 the MAX of AutoIncrement + 1 or 2 fields with the same value.
Cheers, Andrew