sqlserver常用操作(2)——判断关键字段是否重复插入记录
插入数据,判断这条数据是否重复,游标中采用的思想是逐条循环判断是否添加。
方法一:
insert into
dbo.News (NewsTitle,Typeid,TypeName,[content])
select NewsTitle,Typeid,TypeName,[content]
from dbo.News2 b
where b.NewsTitle not in(select NewsTitle from dbo.News)
方法二:
insert into
dbo.News (NewsTitle,Typeid,TypeName,[content])
select NewsTitle,Typeid,TypeName,[content]
from dbo.News2 b
where not exists
(select * from dbo.news where NewsTitle=b.NewsTitle)
游标操作示例一:
declare @cur_pos int
declare mycursor cursor for select NewsTitle,Typeid from dbo.News
declare @col1 char(10)
declare @col2 char(10)
open mycursor
fetch next from mycursor into @col1,@col2
while @@fetch_status<>-1
begin
print @col1+@col2
--select @cur_pos=@cur_pos+1
fetch next from mycursor into @col1,@col2
end
close mycursor
deallocate mycursor
游标操作示例二:
declare @cur_pos int
declare @newsid int
declare @newstitle varchar(20)
declare mycursor cursor for select Newsid,NewsTitle from dbo.News2
open mycursor
fetch next from mycursor into @newsid,@newstitle
while @@fetch_status<>-1
begin
if not exists
(select * from dbo.News where NewsTitle=@newstitle)
begin
insert into dbo.News(NewsTitle,Typeid,TypeName,[content])
select NewsTitle,Typeid,TypeName,[content]
from dbo.News2 where NewsId=@newsid
end
fetch next from mycursor into @newsid,@newstitle
end
close mycursor
deallocate mycursor
方法一:
insert into
dbo.News (NewsTitle,Typeid,TypeName,[content])
select NewsTitle,Typeid,TypeName,[content]
from dbo.News2 b
where b.NewsTitle not in(select NewsTitle from dbo.News)
方法二:
insert into
dbo.News (NewsTitle,Typeid,TypeName,[content])
select NewsTitle,Typeid,TypeName,[content]
from dbo.News2 b
where not exists
(select * from dbo.news where NewsTitle=b.NewsTitle)
游标操作示例一:
declare @cur_pos int
declare mycursor cursor for select NewsTitle,Typeid from dbo.News
declare @col1 char(10)
declare @col2 char(10)
open mycursor
fetch next from mycursor into @col1,@col2
while @@fetch_status<>-1
begin
print @col1+@col2
--select @cur_pos=@cur_pos+1
fetch next from mycursor into @col1,@col2
end
close mycursor
deallocate mycursor
游标操作示例二:
declare @cur_pos int
declare @newsid int
declare @newstitle varchar(20)
declare mycursor cursor for select Newsid,NewsTitle from dbo.News2
open mycursor
fetch next from mycursor into @newsid,@newstitle
while @@fetch_status<>-1
begin
if not exists
(select * from dbo.News where NewsTitle=@newstitle)
begin
insert into dbo.News(NewsTitle,Typeid,TypeName,[content])
select NewsTitle,Typeid,TypeName,[content]
from dbo.News2 where NewsId=@newsid
end
fetch next from mycursor into @newsid,@newstitle
end
close mycursor
deallocate mycursor