March 26, 2013 at 4:15 am
Hi,
We are using Sql server 2008 R2 express. We have two tables,table1(id int,name nvarchar(50)) and table2(id int,name nvarchar(50)). For both tables id is primary key.I want to move rows from table2 to table1 with PK value incrementing i.e i want max(id)+1 of table1 for all rows copying .I tried this query
declare @root int
select @root=max(id+1) from Bgd_common.dbo.table1
insert into Bgd_common.dbo.table1(id,Name) select @root,name from Bgd_common.dbo.table2
But its giving error as ''Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'dbo.Table1'.". How to do?
March 26, 2013 at 4:30 am
As your not incrementing @root your inserting the same value every time.
You would want to row_number the table then add the max ID value to it so that it is incremented and every value is different.
Something like
declare @root int
select @root=max(id) from Bgd_common.dbo.table1
insert into Bgd_common.dbo.table1(id,Name) select @root + ROW_NUMBER() OVER(ORDER BY ID),name from Bgd_common.dbo.table2
March 26, 2013 at 4:53 am
anthony.green (3/26/2013)
As your not incrementing @root your inserting the same value every time.You would want to row_number the table then add the max ID value to it so that it is incremented and every value is different.
Something like
declare @root int
select @root=max(id) from Bgd_common.dbo.table1
insert into Bgd_common.dbo.table1(id,Name) select @root + ROW_NUMBER() OVER(ORDER BY ID),name from Bgd_common.dbo.table2
That worked very fine.Thank you
March 27, 2013 at 4:39 am
Will suggested query work for MS access database?
March 27, 2013 at 4:43 am
Probably not, Google to see if Access supports the ROW_NUMBER() ranking function.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply