sqlserver 导出插入脚本代码
时间:2024-01-26 22:39:18
当然有其它工具可以做这件事,但如果客户不允许你在服务器乱装东西时这个脚本就会有用了。
DECLARE @tbImportTables table(tablename varchar(128), deleted tinyint)
-- append tables which you want to import
Insert Into @tbImportTables(tablename, deleted) values('tentitytype', 1)
Insert Into @tbImportTables(tablename, deleted) values('tattribute', 1)
-- append all tables
--Insert Into @tbImportTables(tablename, deleted) select table_name, 1 from INFORMATION_SCHEMA.tables where table_type = 'BASE TABLE'
DECLARE @tbImportScripts table(script varchar(max))
Declare @tablename varchar(128),
@deleted tinyint,
@columnname varchar(128),
@fieldscript varchar(max),
@valuescript varchar(max),
@insertscript varchar(max)
Declare curImportTables Cursor For
Select tablename, deleted
From @tbImportTables
Open curImportTables
Fetch Next From curImportTables Into @tablename, @deleted
WHILE @@Fetch_STATUS = 0
Begin
If (@deleted = 1)
begin
Insert into @tbImportScripts(script) values ('Truncate table ' + @tablename)
end
Insert into @tbImportScripts(script) values ('SET IDENTITY_INSERT ' + @tablename + ' ON')
set @fieldscript = ''
select @fieldscript = @fieldscript + column_name + ',' from INFORMATION_SCHEMA.columns where table_name = @tablename and data_type not in('timestamp', 'image')
set @fieldscript = substring(@fieldscript, 0, len(@fieldscript))
set @valuescript = ''
select @valuescript = @valuescript + 'case when ' + column_name + ' is null then ''null'' else '''''''' + convert(varchar(max), ' + column_name + ') + '''''''' end +'',''+' from INFORMATION_SCHEMA.columns where table_name = @tablename and data_type not in('timestamp', 'image')
set @valuescript = substring(@valuescript, 0, len(@valuescript) - 4)
set @insertscript = 'select ''insert into ' + @tablename + '(' + @fieldscript + ') values(' + '''+' + @valuescript + ' + '')'' from ' + @tablename
Insert into @tbImportScripts(script) exec ( @insertscript)
Insert into @tbImportScripts(script) values ('SET IDENTITY_INSERT ' + @tablename + ' OFF')
Insert into @tbImportScripts(script) values ('GO ')
Fetch Next From curImportTables Into @tablename, @deleted
End
Close curImportTables
Deallocate curImportTables
Select * from @tbImportScripts
标签:导出插入
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
asp dictionary对象使用介绍
2008-05-30 13:51:00
Django中处理出错页面的方法
2023-08-28 14:41:41
Dreamweaver MX 2004新特点
2008-02-03 11:35:00
Python 数据分析之Beautiful Soup 提取页面信息
2022-04-30 04:34:10
![](https://img.aspxhome.com/file/2023/7/124247_0s.gif)
TypeScript中extends的正确打开方式详解
2024-02-25 07:14:18
php预定义常量
2023-11-14 10:35:27
MS SQL2000 数据库自动备份方法
2010-07-22 19:52:00
Electron点击穿透不规则窗体的透明区域的实现
2024-04-23 09:32:32
![](https://img.aspxhome.com/file/2023/0/135480_0s.png)
Mysql 5.6 "隐式转换"导致的索引失效和数据不准确的问题
2024-01-22 04:07:47
![](https://img.aspxhome.com/file/2023/5/114945_0s.jpg)
python去除列表中的空值元素实战技巧
2023-12-08 12:16:06
Python的组合模式与责任链模式编程示例
2023-05-08 07:03:43
jQuery实现用户注册的表单验证示例
2024-04-10 10:36:12
CSS背景 background 图像属性解读
2008-08-01 18:19:00
python使用psutil模块获取系统状态
2021-12-13 23:36:10
python类中super()和__init__()的区别
2021-04-17 16:03:02
vue2.0 解决抽取公用js的问题
2024-05-28 15:59:28
![](https://img.aspxhome.com/file/2023/0/123110_0s.jpg)
python自动安装pip
2021-04-06 09:30:00
python:批量统计xml中各类目标的数量案例
2021-11-17 05:22:44
![](https://img.aspxhome.com/file/2023/6/88916_0s.jpg)
PHP设计模式(八)装饰器模式Decorator实例详解【结构型】
2023-11-24 05:59:31
![](https://img.aspxhome.com/file/2023/5/111355_0s.jpg)
python向量化与for循环耗时对比分析
2023-12-21 14:14:59
![](https://img.aspxhome.com/file/2023/0/101710_0s.png)