CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user1108948
user1108948

Reputation:

Insert lost data to the table

I have two tables, which have two common column 'StationID'.

Create table t1(ID int, StationID bigint)
insert into t1 values
(0,1111),
(1,2222),
(2,34),
(3,456209),
(56,78979879),
(512,546)
go
Create table t2(StationID bigint, Descr varchar(50))
insert into t2 values
(-1,'test-1'),
(0,'test0'),
(1,'test1'),
(2,'test2'),
(5001,'dummy'),
(5002,'dummy'),
(6001,'dummy')
go

Now we notice that not every t1.StationID is in t2.StationID. Run the script can prove it.

select distinct StationID from t1 as A
where not exists
(select * from t2 as B where B.StationID =A.StationID)

The result is:

StationID
34
546
1111
2222
456209
78979879

Now I want to fill t2 with the lost StationID above, the column Descr can be any dummy data. My real case has thousands records, how to use script to implement it?

Upvotes: 0

Views: 130

Answers (2)

user1945782
user1945782

Reputation: 

INSERT INTO 
    t2 
SELECT DISTINCT 
    stationid, 'dummy'  
FROM 
    t1 
WHERE 
    stationid NOT IN (SELECT stationid FROM t2)

(As an alternative to the others).

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

insert into t2 (StationID, Descr)
select distinct StationID, 'dummy' 
from t1 as A
where not exists
   (select * from t2 as B where B.StationID =A.StationID)

Upvotes: 4

Related Questions