表user
字段userid,username,money,type表userInfo
字段userid,name,usermoney,usertypeuser表中的数据:
userid username money type
1 test 100 1
2 test2 100 0
3 test3 100 1userInfo表中的数据:
userid name usermoney usertype
1 test 100 1
2 test1 100 0 现在想取出user表中的username和userInfo表中name不相等的数据,即test2,test3的所有信息,并插入到userInfo表中,用存储过程应该怎么做?
字段userid,username,money,type表userInfo
字段userid,name,usermoney,usertypeuser表中的数据:
userid username money type
1 test 100 1
2 test2 100 0
3 test3 100 1userInfo表中的数据:
userid name usermoney usertype
1 test 100 1
2 test1 100 0 现在想取出user表中的username和userInfo表中name不相等的数据,即test2,test3的所有信息,并插入到userInfo表中,用存储过程应该怎么做?
select * from user not exists(select * from userinfo)
select * from user where not exists(select * from userinfo)
少了where 汗
select * from user where id in
(select user.id from userinfo,user
where userinfo.name<>user.name)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO userInfo
SELECT * FROM [user] A WHERE NOT EXISTS (SELECT 1 FROM userInfo WHERE A.USERNAME=NAME)
SET NOCOUNT OFF
END
drop table [user]
gocreate table [user]
(
userid int,
username varchar(10),
money int,
type int
)insert into [user](userid,username,money,type) values(1, 'test' , 100, 1)
insert into [user](userid,username,money,type) values(2, 'test2' , 100, 0)
insert into [user](userid,username,money,type) values(3, 'test3 ', 100, 1)if object_id('pubs..[userInfo]') is not null
drop table [userInfo]
gocreate table [userInfo]
(
userid int,
name varchar(10),
usermoney int,
usertype int
)insert into [userInfo](userid,name,usermoney,usertype) values(1, 'test' , 100 , 1)
insert into [userInfo](userid,name,usermoney,usertype) values(2, 'test1' , 100 , 0)
goinsert into [userInfo](userid,name,usermoney,usertype) select userid,username as name,money as usemoney , type as usetype from [user] where username not in (select name as username from userinfo)select * from [userInfo]
drop table [user],[userInfo]userid name usermoney usertype
----------- ---------- ----------- -----------
1 test 100 1
2 test1 100 0
2 test2 100 0
3 test3 100 1(所影响的行数为 4 行)