作为插入到另一个表的字段用。
比如说新闻,有newstitlenote表和newdetail表,分别存放的是基本信息和新闻详细内容
为了保持数据同步所以必须返回唯一标识一条条插入,而不能用insert into...select...
这里用了两种方法:
1游标
SET
ANSI_NULLS
OFF
GO
SET QUOTED_IDENTIFIER ON
GO
-- 唯一标识
declare @currentvalue int
declare @isshow int = 0
declare @cur_checkdate datetime
declare @cur_newsline varchar ( 200 )
declare @cur_quarry varchar ( 50 )
declare @cur_parentid int
declare cur_custer cursor
for
select [ newsday ] , [ newsline ] , [ Quarry ] , [ newstype ] from [ News_Fund_Content ] where newstype = 4 order by newsday desc
-- 打开游标
open cur_custer
-- 读取一行
fetch cur_custer into @cur_checkdate , @cur_newsline , @cur_quarry , @cur_parentid
while ( @@fetch_status = 0 )
begin
insert into [ news_title_note ] ( [ check_date ] , [ newsline ] , [ Quarry ] , [ isshow ] , [ parentid ] )
values ( @cur_checkdate , @cur_newsline , @cur_quarry ,, @isshow , @cur_parentid )
select @currentvalue = @@IDENTITY
-- 唯一标识用作插入到news_classlist用
print @currentvalue
INSERT INTO [ dbo ] . [ news_classlist ] ( [ noteid ] , [ classname ] , [ parentid ] )
values ( @currentvalue , @classname , @newstype )
fetch cur_custer into @cur_checkdate , @cur_newsline , @cur_quarry , @cur_parentid
end
-- 关闭游标
close cur_custer
-- 撤销游标
deallocate cur_custer
GO
SET QUOTED_IDENTIFIER ON
GO
-- 唯一标识
declare @currentvalue int
declare @isshow int = 0
declare @cur_checkdate datetime
declare @cur_newsline varchar ( 200 )
declare @cur_quarry varchar ( 50 )
declare @cur_parentid int
declare cur_custer cursor
for
select [ newsday ] , [ newsline ] , [ Quarry ] , [ newstype ] from [ News_Fund_Content ] where newstype = 4 order by newsday desc
-- 打开游标
open cur_custer
-- 读取一行
fetch cur_custer into @cur_checkdate , @cur_newsline , @cur_quarry , @cur_parentid
while ( @@fetch_status = 0 )
begin
insert into [ news_title_note ] ( [ check_date ] , [ newsline ] , [ Quarry ] , [ isshow ] , [ parentid ] )
values ( @cur_checkdate , @cur_newsline , @cur_quarry ,, @isshow , @cur_parentid )
select @currentvalue = @@IDENTITY
-- 唯一标识用作插入到news_classlist用
print @currentvalue
INSERT INTO [ dbo ] . [ news_classlist ] ( [ noteid ] , [ classname ] , [ parentid ] )
values ( @currentvalue , @classname , @newstype )
fetch cur_custer into @cur_checkdate , @cur_newsline , @cur_quarry , @cur_parentid
end
-- 关闭游标
close cur_custer
-- 撤销游标
deallocate cur_custer
2创建临时表查一条删一条
SET
ANSI_NULLS
OFF
GO
SET QUOTED_IDENTIFIER ON
GO
create table #news
(
newsday datetime ,
newsline varchar ( 100 ),
quarry varchar ( 50 ),
newstype int
)
-- 把所有数据导入到临时表中
insert into #news select [ newsday ] , [ newsline ] , [ Quarry ] , [ newstype ] from [ News_Fund_Content ] where newstype = 4
declare @newsday datetime
declare @quarry varchar ( 50 )
declare @newsline varchar ( 100 )
declare @newstype int
declare @isshow int = 0
declare @classname varchar ( 50 )
set @classname = ' 后勤 '
-- 返回唯一标识
declare @currentvalue int
while ( select count ( 1 ) from #news where newstype = 4 ) > 0
begin
set @newsday = select top 1 @newsday = [ newsday ] , @quarry = [ quarry ] , @newsline = [ newsline ] , @newstype = [ newstype ]
from #news order by newsday desc
insert into [ news_title_note ] ( [ check_date ] , [ newsline ] , [ Quarry ] , [ isshow ] , [ parentid ] )
values ( @newsday , @newsline , @quarry , @isshow , @newstype )
-- 取唯一标识用作下个表的插入
select @currentvalue = @@IDENTITY
print @currentvalue
INSERT INTO [ dbo ] . [ news_classlist ] ( [ noteid ] , [ classname ] , [ parentid ] )
values ( @currentvalue , @classname , @newstype )
-- 在临时表中删除这条新闻
delete #news where newsline = @newsline
end
3当然还可以用程序写~。
GO
SET QUOTED_IDENTIFIER ON
GO
create table #news
(
newsday datetime ,
newsline varchar ( 100 ),
quarry varchar ( 50 ),
newstype int
)
-- 把所有数据导入到临时表中
insert into #news select [ newsday ] , [ newsline ] , [ Quarry ] , [ newstype ] from [ News_Fund_Content ] where newstype = 4
declare @newsday datetime
declare @quarry varchar ( 50 )
declare @newsline varchar ( 100 )
declare @newstype int
declare @isshow int = 0
declare @classname varchar ( 50 )
set @classname = ' 后勤 '
-- 返回唯一标识
declare @currentvalue int
while ( select count ( 1 ) from #news where newstype = 4 ) > 0
begin
set @newsday = select top 1 @newsday = [ newsday ] , @quarry = [ quarry ] , @newsline = [ newsline ] , @newstype = [ newstype ]
from #news order by newsday desc
insert into [ news_title_note ] ( [ check_date ] , [ newsline ] , [ Quarry ] , [ isshow ] , [ parentid ] )
values ( @newsday , @newsline , @quarry , @isshow , @newstype )
-- 取唯一标识用作下个表的插入
select @currentvalue = @@IDENTITY
print @currentvalue
INSERT INTO [ dbo ] . [ news_classlist ] ( [ noteid ] , [ classname ] , [ parentid ] )
values ( @currentvalue , @classname , @newstype )
-- 在临时表中删除这条新闻
delete #news where newsline = @newsline
end
只不过觉得比较麻烦还得建个项目啥的。
遇到text字段无法导入的问题,在博问中请教了下,
有说设定@newscontent 时设定为varchar并且设定的范围大点可解决导入text字段的问题。
试试再说~先谢过~
网上说游标比较慢,具体自己没有测试过,所以先推荐第二种方法吧。
欢迎讨论指正~