如何从数据库直接生成 insert 语句？
表里已经有数据，
我想生成插入数据的 insert 语句，
每笔记录生成一条 insert 语句。
问题点数：20、回复次数：35Top
1 楼AndXie99（谢波）回复于 2005-07-20 15:46:00 得分 5
-- ======================================================
--根据表中数据生成insert语句的存储过程
--建立存储过程，执行 proc_insert 表名
--感谢Sky_blue
-- ======================================================
CREATE proc proc_insert (@tablename varchar(256))
as
begin
set nocount on
declare @sqlstr varchar(4000)
declare @sqlstr1 varchar(4000)
declare @sqlstr2 varchar(4000)
select @sqlstr='select ''insert '+@tablename
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= ' values ( ''+'
select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
-- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
else '''NULL'''
end as col,a.colid,a.name
from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36
)t order by colid
select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
-- print @sqlstr
exec( @sqlstr)
set nocount off
end
GO
Top
2 楼vivianfdlpw（）回复于 2005-07-20 15:49:36 得分 3
如果有日志，可以用log explorer导出sql脚本
没有的话，下面是一个例子：
declare @tb table(ID int,name varchar(20))
insert @tb select 1,'AAA' union select 2,'BBB'
declare cur cursor for select * from @tb
open cur
declare @ID int,@name varchar(20)
fetch next from cur into @ID,@name
while @@fetch_status=0
begin
print 'insert @tb(ID,name) select '+convert(varchar,@ID)+','''+@name+''''
fetch next from cur into @ID,@name
end
close cur
deallocate cur
--结果
/*
（所影响的行数为 2 行）
insert @tb(ID,name) select 1,'AAA'
insert @tb(ID,name) select 2,'BBB'
*/
Top
3 楼surn（孤影.*）回复于 2005-07-20 15:51:08 得分 1
^o^Top
4 楼Eric2000（消灭小日本）回复于 2005-07-20 17:50:06 得分 1
用PB啊！！
在PB中运行查询，然后将查询结果“另存为”*.sql文件，里面包含建表语句和所有的insert语句。
Top
5 楼quakecs（@_@）回复于 2005-07-20 18:19:43 得分 0
我使用了 andxie99 的存储过程，
在查询分析器中可以正确的得到结果。
现在我想把结果存到一个文本文件中。
命令如下：
exec master..xp_cmdshell 'osql -U sa -P sa -S myserver -d mydb -h-1 -u -q "exec proc_insert ''mytable''" -o D:\a.txt'
结果如下：
=============
INSERT INTO [mytable] ([adno],[adresouemid],[contnum],
[bdt],[edt],[cdt]) values (2,1,N'AD089','02 26 2005 12:00
AM','02 24 2005 12:00AM',NULL)
INSERT INTO [mytable] ([adno],[adresouemid],[contnum],
[bdt],[edt],[cdt]) values (3,1,N'AD008','01 31 2005 12:00
AM','02 24 2005 12:00AM',NULL)
INSERT INTO [mytable] ([adno],[adresouemid],[contnum],
[bdt],[edt],[cdt]) values (4,1,N'11','02 18 2005 12:00AM'
,'02 25 2005 12:00AM','02 21 2005 3:41PM')
（11 行受到影响）
1>
==========================
问题有两个：
1、两行数据之间有很大一片空白。
2、最后还有这两行我不想要的：
（11 行受到影响）
1>Top
6 楼quakecs（@_@）回复于 2005-07-20 18:21:11 得分 0
两外还有一个问题，
3、每行 insert 语句都被折断成 3 行了，我希望每个 insert 语句都只有一行。Top
7 楼poonzsp（怒放的生命）回复于 2005-07-21 12:06:21 得分 1
studyTop
8 楼hglhyy（為人民币服务！）回复于 2005-07-21 12:12:36 得分 1
存储过程写得好！Top
9 楼quakecs（@_@）回复于 2005-07-21 12:17:41 得分 0
我又尝试了使用 isql，
但是生成的文件每行只能有 256 个字符，而且后面的都被截断了（不是换行）。
最后，我用 isqlw，
但是生成的文件是乱码。Top
10 楼jixiaojie（太多借口）回复于 2005-07-21 12:31:14 得分 1
upTop
11 楼vivianfdlpw（）回复于 2005-07-21 12:36:46 得分 0
语法
isql
[-?] |
[-L] |
[
{
{-U login_id [-P password]}
| -E
}
[-S server_name] [-H wksta_name] [-d db_name]
[-l time_out] [-t time_out] [-h headers]
[-s col_separator] [-w column_width] [-a packet_size]
[-e] [-x max_text_size]
[-c cmd_end] [-q "query"] [-Q "query"]
[-n] [-m error_level] [-r {0 | 1}]
[-i input_file] [-o output_file] [-p]
[-b] [-O]
]
-w column_width
允许用户设置屏幕输出的宽度。默认为 80 个字符。当输出行达到其最大屏幕宽度时，会拆分为多个行。
-x max_text_size
指定返回文本数据的最大长度，以字节为单位。长于 max_text_size 的文本值将被截断。如果未指定 max_text_size，则文本数据在 4096 字节处截断。
Top
12 楼quakecs（@_@）回复于 2005-07-21 13:41:00 得分 0
谢谢 vivianfdlpw，
我读了几遍 isql 的参数，
不知道为什么，
居然都没注意看到 -x 这个参数。Top
13 楼quakecs（@_@）回复于 2005-07-21 14:02:29 得分 0
我试了，
-x 这个参数总是不起作用。Top
14 楼quakecs（@_@）回复于 2005-07-21 14:30:21 得分 0
数据还是被截断，
郁闷！Top
15 楼Corporal（山中巴人）回复于 2005-07-22 13:31:59 得分 0
我也在试Top
16 楼quakecs（@_@）回复于 2005-07-22 13:47:04 得分 0
如果试成功了，
请告知一声。
谢谢。Top
17 楼quakecs（@_@）回复于 2005-07-25 08:48:53 得分 0
自己up一下。Top
18 楼xueguang（xg）回复于 2005-07-25 09:30:19 得分 1
在查询分析器是可以调整每列最多的字符数的，在 工具->选项->结果中设置，最大为8192Top
19 楼quakecs（@_@）回复于 2005-07-25 11:15:18 得分 0
我已经将工具->选项->结果中设置，最大为3000了，
但是数据还是被截断。Top
20 楼quakecs（@_@）回复于 2005-07-25 18:09:41 得分 0
自己 up 一下。Top
21 楼quakecs（@_@）回复于 2005-07-26 17:12:29 得分 0
再 up 一下。Top
22 楼laker_tmj（laker）回复于 2005-07-26 17:27:58 得分 1
up learnTop
23 楼maomaoma（）回复于 2005-07-26 17:33:26 得分 0
你是用什么工具调用库里创建的存储过程啊？
我也遇到了同样的问题。我是在sql里创建了一个函数，然后再pb里面调用，最后获取的内容只有 255字节，你是不是也是同样的问题？
不过我的更奇怪，同样的函数我曾在一台机器上执行无误，但在另外4台机器上只取了255字节内容。Top
24 楼quakecs（@_@）回复于 2005-07-27 08:56:34 得分 0
我用 isql 调用存储过程。
遇到跟你一样的问题。
我现在 isql、osql、isqlw 这三个工具都用了，
但是都是各有问题。Top
25 楼quakecs（@_@）回复于 2005-07-28 09:39:58 得分 0
使用 isql 导致数据被截断的问题还是没有解决，
高手在哪里？Top
26 楼quakecs（@_@）回复于 2005-08-01 13:10:16 得分 0
自己再顶一下。Top
27 楼aqandy（欲凡）回复于 2005-08-03 08:36:31 得分 1
mark
Top
28 楼zxjcool（cool）回复于 2005-08-03 11:11:49 得分 0
quakecs(@_@) 我按照你所说的方法，仅得到第一行insert语句，还有其它的insert语句没有出现，这是为什么啊Top
29 楼operfume（橘子香水）回复于 2005-08-03 12:50:42 得分 1
用pb最简单Top
30 楼quakecs（@_@）回复于 2005-08-05 09:37:53 得分 0
cool
不过我一直没有遇到你说的情况。
你是直接在查询分析器中执行存储过程，还是使用 osql 等工具来执行？
Top
31 楼playyuer（退休干部 卧鼠藏虫）回复于 2005-08-06 16:28:43 得分 2
http://microshaoft.cnblogs.com/archive/2005/08/06/208876.html
T-SQL 存储过程: (20050802修订无误版) 根据基本表结构及其数据生成 INSERT INTO ... 的 SQL
摘要:在网上能够搜索出的版本主要有两个:
1. CSDN Sky_blue 所作: proc_insert (可 google)
2. CSDN playyuer 所作: spGenInsertSQL (可 google)
但这两个版本的程序都曾收录到:
http://www.cnblogs.com/kasafuma/articles/109922.html
但这两个版本程序都有局限性:
如果字段太多或字段值的内容太多而无法生成完整正确的 insert into ... 的 SQL!
varchar 变量容量不够大!
,应该算 Microsoft SQL Server 的缺陷
这个问题已经存在了很久了,长达至少两年多了!
今天终于被窝想到了解决办法,很简单: ...
Top
32 楼quakecs（@_@）回复于 2005-08-08 10:12:37 得分 0
to playyer:
如果我只在查询分析器中执行存储过程，结果总是正确的。
但是如果我在 bat 文件中调用 isql 命令的话，
得到的结果中的每行总是被截断为 255 字节。Top
33 楼yiyezhiqiu（一叶知秋）回复于 2005-08-08 13:47:50 得分 1
有这样的工具啊 你在网上找一下很多啊Top
34 楼quakecs（@_@）回复于 2005-08-08 15:46:56 得分 0
介绍一下有什么工具嘛！Top
35 楼quakecs（@_@）回复于 2005-08-08 17:54:50 得分 0
这个问题已经困扰我好几天了，
也上网查了很多资料，
不过还是没有在调用 isql 时解决问题。Top
