一个把表中数据生成Insert脚本的通用存储过程
来源:互联网 发布:放心的网络代销从哪找 编辑:程序博客网 时间:2024/06/06 02:57
--drop proc p_createinsertscript
create proc p_createinsertscript(@table_name SYSNAME)
as
begin
SET NOCOUNT ON
--DECLARE @table_name SYSNAME
DECLARE @handle_big_binary BIT
DECLARE @column_names BIT
DECLARE @strsql varchar(4000)
-- ////////////////////
-- -> Configuration
--SET @table_name = 't1'
SET @handle_big_binary = 1
SET @column_names = 1
-- <- Configuration
-- ////////////////////
DECLARE @object_id INT
DECLARE @schema_id INT
--SELECT * FROM sys.all_objects
SELECT @object_id = object_id, @schema_id = schema_id
FROM sys.tables
WHERE object_id = OBJECT_ID(@table_name)
DECLARE @columns TABLE (column_name SYSNAME, ordinal_position INT, data_type SYSNAME, data_length INT, is_nullable BIT)
-- Get all column information
INSERT INTO @columns
SELECT column_name, ordinal_position, data_type, character_maximum_length, CASE WHEN is_nullable = 'YES' THEN 1 ELSE 0 END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA_NAME(@schema_id)
AND TABLE_NAME = OBJECT_NAME(@object_id)
DECLARE @select VARCHAR(MAX)
DECLARE @insert VARCHAR(MAX)
DECLARE @crlf CHAR(2)
DECLARE @sql VARCHAR(MAX)
DECLARE @first BIT
DECLARE @pos INT
SET @pos = 1
SET @crlf = CHAR(13) + CHAR(10)
WHILE EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position >= @pos)
BEGIN
DECLARE @column_name SYSNAME
DECLARE @data_type SYSNAME
DECLARE @data_length INT
DECLARE @is_nullable BIT
-- Get information for the current column
SELECT @column_name = column_name, @data_type = data_type, @data_length = data_length, @is_nullable = is_nullable
FROM @columns
WHERE ordinal_position = @pos
-- Create column select information to script the name of the source/destination column if configured
IF (@select IS NULL)
SET @select = ' ''' + QUOTENAME(@column_name)
ELSE
SET @select = @select + ','' + ' + @crlf + ' ''' + QUOTENAME(@column_name)
-- Handle NULL values
SET @sql = ' '
SET @sql = @sql + 'CASE WHEN ' + QUOTENAME(@column_name) + ' IS NULL THEN ''NULL'' ELSE '
-- Handle the different data types
IF (@data_type IN ('bigint', 'bit', 'decimal', 'float', 'int', 'money', 'numeric',
'real', 'smallint', 'smallmoney', 'tinyint'))
BEGIN
SET @sql = @sql + 'CONVERT(VARCHAR(40), ' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type IN ('char', 'nchar', 'nvarchar', 'varchar'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(' + QUOTENAME(@column_name) + ', '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type = 'date')
BEGIN
SET @sql = @sql + '''CONVERT(DATE, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(3), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'time')
BEGIN
SET @sql = @sql + '''CONVERT(TIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(5), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime2')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME2, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'smalldatetime')
BEGIN
SET @sql = @sql + '''CONVERT(SMALLDATETIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(4), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'text')
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(VARCHAR(MAX), ' + QUOTENAME(@column_name) + '), '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type IN ('ntext', 'xml'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(NVARCHAR(MAX), ' + QUOTENAME(@column_name) + '), '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type IN ('binary', 'varbinary'))
BEGIN
-- Use udf_varbintohexstr_big if available to avoid cutted binary data
IF (@handle_big_binary = 1)
SET @sql = @sql + ' dbo.udf_varbintohexstr_big (' + QUOTENAME(@column_name) + ')'
ELSE
SET @sql = @sql + ' master.sys.fn_varbintohexstr (' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type = 'timestamp')
BEGIN
SET @sql = @sql + '''CONVERT(TIMESTAMP, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'uniqueidentifier')
BEGIN
SET @sql = @sql + '''CONVERT(UNIQUEIDENTIFIER, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(16), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'image')
BEGIN
-- Use udf_varbintohexstr_big if available to avoid cutted binary data
IF (@handle_big_binary = 1)
SET @sql = @sql + ' dbo.udf_varbintohexstr_big (CONVERT(VARBINARY(MAX), ' + QUOTENAME(@column_name) + '))'
ELSE
SET @sql = @sql + ' master.sys.fn_varbintohexstr (CONVERT(VARBINARY(MAX), ' + QUOTENAME(@column_name) + '))'
END
ELSE
BEGIN
PRINT 'ERROR: Not supported data type: ' + @data_type
RETURN
END
SET @sql = @sql + ' END'
-- Script line end for finish or next column
IF EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position > @pos)
SET @sql = @sql + ' + '', '' +'
ELSE
SET @sql = @sql + ' + '
-- Remember the data script
IF (@insert IS NULL)
SET @insert = @sql
ELSE
SET @insert = @insert + @crlf + @sql
SET @pos = @pos + 1
END
-- Close the column names select
SET @select = @select + ''' +'
-- Print the INSERT INTO part
PRINT 'SELECT ''INSERT INTO ' + @table_name + ''' + '
set @strsql='SELECT ''INSERT INTO ' + @table_name + ''' + '
-- Print the column names if configured
IF (@column_names = 1)
BEGIN
PRINT ' ''('' + '
set @strsql = @strsql + ' ''('' + '
PRINT @select
set @strsql = @strsql + @select
PRINT ' '')'' + '
set @strsql = @strsql + ' '')'' + '
END
PRINT ' ''VALUES ('' +'
set @strsql = @strsql + ' ''VALUES ('' +'
-- Print the data scripting
PRINT @insert
set @strsql = @strsql + @insert
-- Script the end of the statement
PRINT ' '')'''
set @strsql = @strsql + ' '')'''
PRINT ' FROM ' + @table_name
set @strsql = @strsql + ' FROM ' + @table_name
exec(@strsql)
end
--usage
exec p_createinsertscript 't1'
--the result is as below
INSERT INTO t1([a],[b])VALUES (1, 'a')
INSERT INTO t1([a],[b])VALUES (1, 'a')
- 一个把表中数据生成Insert脚本的通用存储过程
- 将表中的数据生成Insert脚本的存储过程!!!
- SQLSERVER表数据生成INSERT语句(通用存储过程)
- 根据表中数据生成insert语句的存储过程
- 根据表中数据生成insert语句的存储过程
- 根据表中数据生成insert语句的存储过程
- 根据表中数据生成insert语句的存储过程
- MS SQL server 生成 insert 数据的存储过程脚本
- 将数据库表中的数据生成Insert脚本的存储过程!!!
- 将数据库表中的数据生成Insert脚本的存储过程!!!
- 将数据库表中的数据生成Insert脚本的存储过程(改版)
- MSSQL2K 将表数据生成INSERT SQL脚本的存储过程
- SQL SERVER:把表里的数据导出成为INSERT INTO脚本的存储过程
- SQL SERVER:把表里的数据导出成为INSERT INTO脚本的存储过程
- (转)根据表中数据生成insert语句的存储过程
- (转)根据表中数据生成insert语句的存储过程(另一种方法)
- SQL脚本--(通用存储过程)如何删除表中某个字段重复的数据
- 将表数据生成SQL脚本的存储过程
- 解决多多IP情况下,SSL无法正常访问
- 文本分类和聚类有什么区别?
- 为何使用WEBBROWSER的对象CLICK事件不能生效
- 网络流量单位bps与系统单位byte转换 (转)
- 性能问题算不算bug?
- 一个把表中数据生成Insert脚本的通用存储过程
- FTP对IPv6和NAT的扩展
- 经典SQL语句大全
- tomcat log4j 日志输出设置
- nvarchar varchar区别
- IIS配置
- 网站开通了!
- exOsip Register 心跳处理
- 珍惜宠物,珍惜爱