0

I have two database,where two table are same with all schema. I want to move specific records of employees and employeesrates with all columns of both tables. below is the query.

CREATE TABLE #emp
(
empID INT IDENTITY(1, 1) primary key ,
Firstname varchar(20)
);

CREATE TABLE #empRates
(
ID INT IDENTITY(1, 1) primary key ,
empid int, -- foreign key from #emp
rate decimal(10,3),
startdate datetime,
enddate datetime,
);
insert into #emp (firstname) values('First')
insert into #emp (firstname) values('Second')
insert into #emp (firstname) values('Third')

insert into #empRates(empid,rate,startdate,enddate) values(1,10,'2020/01/10','2020/01/20')
insert into #empRates(empid,rate,startdate,enddate) values(1,15,'2020/01/20','2020/01/30')

insert into #empRates(empid,rate,startdate,enddate) values(2,10,'2020/01/10','2020/01/20')
insert into #empRates(empid,rate,startdate,enddate) values(3,15,'2020/01/20','2020/01/30')


select * from #emp
select * from #empRates

drop table #emp
drop table #empRates

Here both database on same server. Database1 and Database2. below my query which tried.

insert into database2..empRates(empid,rate,startdate,enddate) select empid,rate,startdate,enddate

from database1..empRates

Here my problem is both database have different records,so identity are different,so after insert other employee rates get displayed for another like mashed up. I am using sql server 2012. can you please provide the way.

result
  • 45
  • 1
  • 6
  • 1
    You could only do that if firstname is unique in your tables otherwise there is no relationship between them. – Atk Mar 21 '20 at 16:43
  • This sort of task is facilitated when tables have a natural key instead of or in addition to the identity surrogate key. – Dan Guzman Mar 21 '20 at 16:50
  • So you need to copy the records to some sort of temporary table first, and do a mapping between old and new ids, so that empRates can be inserted with ids that match emp? – James Z Mar 21 '20 at 18:17
  • Actually duplicate is not an issue, issue is that if I I am moving employee from database1 to database2 , in database2 same identity may be already occupied by another employee.so after import in database2 **empRates** may be wrong-inserted for oher employee. – result Mar 22 '20 at 10:28

1 Answers1

0

You should take a look at this post --> How to turn IDENTITY_INSERT on and off using SQL Server 2008?

This way you can specify value for id column during insert, so rows on destination databases will keep IDs from origin.

Hope it helps!

David
  • 464
  • 1
  • 4
  • 7
  • Actually duplicate is not an issue, issue is that if I I am moving employee from database1 to database2 , in database2 same identity may be already occupied by another employee.so after import in database2 **empRates** may be wrong-inserted for oher employee. – result Mar 22 '20 at 10:28