if object_id('tempdb..#tmp') is not null drop table #tmp GO ----创建测试数据 create table #tmp(id int,CityName varchar(20),flag int) insert #tmp select 201, '北京市', 2 union all select 202, '天津市', 2 union all select 203, '河北省', 2 union all select 204, '山西省', 2 union all select 205, '内蒙古自治区', 2 union all select 211, '辽宁省', 2 union all select 212, '吉林省', 2 union all select 213, '黑龙江省', 2 ----扫描上面的测试表,生成insert脚本 declare @str varchar(8000) set @str = '' select @str = @str + ' union all select ' + rtrim(id) + ',''' + CityName + ''',' + rtrim(flag) from #tmp set @str = stuff(@str,1,10,'') /*去掉开头的" union all"字符串*/ print @str /* 将print出的内容复制到查询分析器的编辑框中,保存为sql文件即可. 如果表的行数太多,可以多定义几个@str1,@str2,...@strn,然后连接起来.例如: */ declare @str1 varchar(8000),@str2 varchar(8000),@str3 varchar(8000) set @str1 = '' select @str1 = @str1 + ' union all select ' + rtrim(id) + ',''' + CityName + ''',' + rtrim(flag) from #tmp where id between 201 and 203 /*指定范围,防止@str1超过8000*/ set @str1 = stuff(@str1,1,10,'') /*去掉开头的" union all"字符串*/set @str2 = '' select @str2 = @str2 + ' union all select ' + rtrim(id) + ',''' + CityName + ''',' + rtrim(flag) from #tmp where id between 204 and 206 /*指定范围,防止@str2超过8000*/set @str3 = '' select @str3 = @str3 + ' union all select ' + rtrim(id) + ',''' + CityName + ''',' + rtrim(flag) from #tmp where id > 207 /*指定范围,防止@str3超过8000*/print @str1 + @str2 + @str3 /* 将print出的内容复制到查询分析器的编辑框中,保存为sql文件即可. */drop table #tmp
表
201 北京市 2
202 天津市 2
203 河北省 2
204 山西省 2
205 内蒙古自治区 2
211 辽宁省 2
212 吉林省 2
213 黑龙江省 2
....
怎么把表里的数据 生成insert into语句呢?
drop table #tmp
GO
----创建测试数据
create table #tmp(id int,CityName varchar(20),flag int)
insert #tmp
select 201, '北京市', 2 union all
select 202, '天津市', 2 union all
select 203, '河北省', 2 union all
select 204, '山西省', 2 union all
select 205, '内蒙古自治区', 2 union all
select 211, '辽宁省', 2 union all
select 212, '吉林省', 2 union all
select 213, '黑龙江省', 2
----扫描上面的测试表,生成insert脚本
declare @str varchar(8000)
set @str = ''
select @str = @str + ' union all select ' + rtrim(id) + ',''' + CityName + ''',' + rtrim(flag)
from #tmp
set @str = stuff(@str,1,10,'') /*去掉开头的" union all"字符串*/
print @str
/*
将print出的内容复制到查询分析器的编辑框中,保存为sql文件即可.
如果表的行数太多,可以多定义几个@str1,@str2,...@strn,然后连接起来.例如:
*/
declare @str1 varchar(8000),@str2 varchar(8000),@str3 varchar(8000)
set @str1 = ''
select @str1 = @str1 + ' union all select ' + rtrim(id) + ',''' + CityName + ''',' + rtrim(flag)
from #tmp where id between 201 and 203 /*指定范围,防止@str1超过8000*/
set @str1 = stuff(@str1,1,10,'') /*去掉开头的" union all"字符串*/set @str2 = ''
select @str2 = @str2 + ' union all select ' + rtrim(id) + ',''' + CityName + ''',' + rtrim(flag)
from #tmp where id between 204 and 206 /*指定范围,防止@str2超过8000*/set @str3 = ''
select @str3 = @str3 + ' union all select ' + rtrim(id) + ',''' + CityName + ''',' + rtrim(flag)
from #tmp where id > 207 /*指定范围,防止@str3超过8000*/print @str1 + @str2 + @str3
/*
将print出的内容复制到查询分析器的编辑框中,保存为sql文件即可.
*/drop table #tmp
谢谢了