I Use visual Basic 8.0 and Mysql 5.0
I have two exactly the same Tables:
Table1:
RecordID , Integer = primary key auto_increment NOT NULL
Articlenr , Integer
Articlename, Char(20)

Table2:
RecordID , Integer = primary key auto_increment NOT NULL
Articlenr , Integer
Articlename, Char(20)

Table1: has two records:
RecordID, Articlenr ,Articlename
1 , 1000, Hamer
2 , 2000, saw

Table2:
Has no records!!


I want to add all the records from table1 to table2.
I want to do this 10 times

if I execute the following statement:
INSERT into Table2 SELECT * FROM Table1 ;
Everything works perfectly. The two records are added to table2
But if I execute the stament a second time get the following message:
Duplicate entry '1' for key 1

I now i must do something with "duplicate key update" in the Insert statement.

Can someone give me the statement correct a.u.b.

Thanks in advance,

André

RecordID is an auto-incremented primary key, it allows only unique entries. Once you've executed the first time and populated that field, you cannot use that value again. Consider doing an insert statement on specific fields

Insert Into Table2 (Articlenr, Articlename) Select Articlenr, Articlename From Table1

If it is important to pull over the RecordID from Table1, add a new column to Table2, perhaps called Table1RecordID. And then modify the above SQL statement.

Insert Into Table2 (Table1RecordID, Articlenr, Articlename) Select RecordID, Articlenr, Articlename From Table1

Thanks this works fine!!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.