SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc pGetInsertSQL (@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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
create table test(name varchar(20))
insert test
select '1' union all
select '2' union all
select '3' union all
select '4'
go
exec pGetInsertSQL 'test'
-------------------------------------------------
insert test (name) values ( '1')
insert test (name) values ( '2')
insert test (name) values ( '3')
insert test (name) values ( '4')
分享到:
相关推荐
可以支持导出单表的建表语句,也可以支持单库所有建表语句的导出以及同服务器跨库建表语句的导出。
python自动生成sql建表语句,可生成oracle,mysql,teradata建表语句,传入excel,生成建表语句
oracle向mysql建表语句的迁移。 直接表结构的生成sql脚本
SQL sever 2000各种查询语句和建表语句
达梦数据库建表操作SQL语句大全.pdf
根据Excel生成建表语句sql源码
sql语句建表,可以帮助多了解sql的语法。比较简单的建表语句。
基于oracle或者mysql生成自动建表同步源数据表结构及注释
SQL简单入门语句建立表; SQL简单入门语句建立表, SQL简单入门语句建立表
Quartz 数据库建表语句 大全!
本工具是生成oracle数据库建表语句的sql工具,利用excel实现,需要开启宏
Excel根据表格,生成sql语句 ,生成建表语句,自行到数据库中执行, 需要excel启用宏,按规则填充表格后可生成表格目录及生成sqlserver语句。
SQL建表语句
SQL_sever_2000各种查询语句和建表语句
建表语句.sql
hive建表语句hive建表语句hive建表语句hive建表语句hive建表语句hive建表语句
34个mysql查询语句,包含思路与建表语句。查询语句出处已经找不大了
sybase库中导出全部表的oracle、mysql和sybase格式的建表语句
数据库建表操作SQL语句大全,对sql有初步的了解,适合新手初步学习
通过excel创建n个sheet页,每个sheet页的内容为数据库表见表字段,一键生成数据库见表语句