auto-increment doesnt work - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

auto-increment doesnt work

Author

Topic

alejandro
Starting Member

6 Posts

Posted - 2010-06-29 : 04:18:06

Hello masters and students, I have a weird issue and Im nearly to hit my head on the wall javascript:insertsmilie('

')

Well... I have 2 tables. Lets say:

TABLE B:
| Field | Type | Null | Key | Default | Extra |
x' ... ... PRI ...
z' ... ... ...

TABLE B:
| Field | Type | Null | Key | Default | Extra |
x ... ... PRI ...
z ... ... ...
y nt(11) ... PRI ... auto-increment

Note: I use both 2 fields as primary keys in Table B.

Problem:
For most rows, the below query succeeds:
INSERT INTO tableB (x,z) SELECT x',z' FROM tableA where ...<condition>
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
I see that the specific row from tableB is copied to tableA and the y value is incremented by one...

BUT... for a specific value, it fails to increment the y field:
INSERT INTO tableB (x,z) SELECT x',z' FROM tableA where ...<condition>
ERROR 1062 (23000): Duplicate entry '.......' for key 1

It seems that if fails to increment the y value and when it inserts it into the tableB, it already has a duplicate with same primary key values(x and y) and if fails.
As I run a script with many many values, its not easy for me to define each time a specific value for auto_increment.

Any ideas?

Thanks