September 24, 2008 at 5:31 am
I am having two tables let say
create table test1 (testId int primary key)
create table test2 (testId int primary key)
Say in test1 I am having 5 records (1,2,3,4,5) then I had fired query
insert into test2 select * from test1
Again i had added 5 more record into test1 (6,7,8,9,10) Now i want to insert all 10 (1,2,3,4,5,6,7,8,9,10) into test2
As i fired query 'insert into test2 select * from test1' System gives error of primary keep.
Please help
September 24, 2008 at 5:43 am
Primary Key can only contain unique values. You second insert tries to add value 1, 2, 3, 4 and 5 to the primary key but these values allready exist, thus generating the error.
You can remove the PK from table 2 and allow duplicate entries, or you should only insert the values that are not present in table 2.
September 24, 2008 at 7:45 am
insert into test2
select * from test1 as t1
where not exists(select * from test2 where pk=t1.pk)
Failing to plan is Planning to fail
October 8, 2008 at 12:34 am
Madhivanan (9/24/2008)
insert into test2select * from test1 as t1
where not exists(select * from test2 where pk=t1.pk)
thanx for this....
actually what if existing entry is updated....???
October 8, 2008 at 1:06 am
If you need to update existing records and insert new records you need to program a kind of MERGE command. Several samples can be found on this site.
Here's a very basic sample:
create table #Table_1 (ID INT PRIMARY KEY, Column1 varchar(50))
create table #Table_2 (ID INT PRIMARY KEY, Column1 varchar(50))
-- first run: insert 4 record in table 1
INSERT INTO #Table_1 (ID, Column1) VALUES (1, 'value 1')
INSERT INTO #Table_1 (ID, Column1) VALUES (2, 'value 2')
INSERT INTO #Table_1 (ID, Column1) VALUES (3, 'value 3')
INSERT INTO #Table_1 (ID, Column1) VALUES (4, 'value 4')
-- first run: insert all records in table 2 (no check for existing records yet!!)
INSERT INTO #Table_2
SELECT * FROM #Table_1
-- second run: insert additional records in table 1
INSERT INTO #Table_1 (ID, Column1) VALUES (5, 'value 5')
INSERT INTO #Table_1 (ID, Column1) VALUES (6, 'value 6')
INSERT INTO #Table_1 (ID, Column1) VALUES (7, 'value 7')
INSERT INTO #Table_1 (ID, Column1) VALUES (8, 'value 8')
-- second run: update an existing record in table 1
UPDATE #Table_1 SET Column1 = 'value 9' WHERE ID = 3
-- second run: update all existing records in table 2
UPDATE #Table_2 SET Column1 = #Table_1.Column1
from #table_2 INNER JOIN #Table_1 ON #table_1.ID = #Table_2.ID
-- second run: insert all new records in table 2
INSERT INTO #Table_2
SELECT * FROM #Table_1
where not exists
(select * from #table_2 where #table_1.ID = #Table_2.ID)
SELECT * FROM #Table_1
SELECT * FROM #Table_2
drop table #Table_1
drop table #Table_2
Like I said, this code is very basic and must be optimized with WHERE clauses to only update changed records, etc.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply