CODEWITHSUNDEEP
mysqlsqlsql-server
rrrocky
rrrocky

Reputation: 696

Join 2 tables with same PRIMARY key that is autoincrementing too

I have 2 tables with both of them having the first column as PRIMARY which is also an auto incrementing. First table has 67 entries starting from 1 to 67 and the second table has 48 entries. Both of them have same columns. I want to take the content from Table2 and insert them into Table1 such that the next entry in Table1 starts from 68. Finally I will have 115 entries in Table1 and the PRIMARY column will also show that. I tried this:

INSERT INTO `Table1` SELECT * FROM `Table2`

But it said 

#1062 - Duplicate entry '1' for key 'PRIMARY'

What do I do?

Upvotes: 1

Views: 192

Answers (4)

Vijay
Vijay

Reputation: 8451

try out this...

INSERT INTO Table1 (col2, col3,...)
select col2, col3,... from Table2

you can do it with n number of columns... Here you need to specify column name on that you wish to insert data other than identity column(Auto Increment Column).

Upvotes: -1

Justin Lessard
Justin Lessard

Reputation: 11901

SET IDENTITY_INSERT database_name.schema_name.table ON 

--Do Insert Here

SET IDENTITY_INSERT database_name.schema_name.table OFF

Make sure there is no duplicate id thought.

See this page

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166366

You need to specify the columns you wish to enter, without the identity column.

Something like

INSERT INTO `Table1` (column1, column2,...,columnn) 
SELECT column1, column2,...,columnn FROM `Table2`

Upvotes: 3

juergen d
juergen d

Reputation: 204756

Name the columns you want to insert and leave the auto_incrment columns from the insert

INSERT INTO `Table1` (col2, col3, col4)
select col2, col3, col4 from `table2`

Upvotes: 5

Related Questions