将数据库表中的数据生成Insert脚本的存储过程!!!

最新推荐文章于 2025-11-26 13:50:39 发布

原创最新推荐文章于 2025-11-26 13:50:39 发布 · 947 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #数据库 #脚本 #存储 #float #join

数据库专栏收录该内容

2 篇文章

订阅专栏

博客给出了一段数据库存储脚本，通过创建存储过程，获取指定表的所有字段名称，根据不同数据类型处理字段，最终生成插入数据的语句，涉及表名、列名、数据类型等操作。

CREATE Procedure DBGenData
?@tableName? Varchar(100)?--表名
AS
DECLARE @columnName VARCHAR(100)??--列名
DECLARE @TypeName VARCHAR(100)??--数据类型
DECLARE @columns VARCHAR(3000)???--
DECLARE @columnsAndhead VARCHAR(3000)?--

SET @columnsAndhead = 'INSERT INTO '+@tableName+'('
SET @columns = ''

--获取表的所有字段名称
DECLARE a? CURSOR FOR
SELECT a.[NAME] AS ColumnName ,b.[NAME] AS TypeName
FROM syscolumns a INNER JOIN systypes b ON a.xtype=b.xtype
WHERE a.[id]=(SELECT [id] FROM sysobjects WHERE [NAME]=@tableName)

OPEN a
FETCH NEXT FROM a INTO @columnName ,@TypeName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @TypeName IN ('bigint','bit','decimal','float','int','money','numeric','real','smallint','smallmoney','tinyint')
BEGIN
SET @columns = @columns + 'ISNULL(CAST('+@columnName +' AS VARCHAR),'''')+'',''+'
END
ELSE
BEGIN
SET @columns = @columns+? '''''''''+? ISNULL(CAST('+@columnName +' AS VARCHAR),'''')+ ''''''''+'',''+'
END
SET @columnsAndhead = @columnsAndhead + @columnName +','?????
FETCH NEXT FROM a INTO @columnName ,@TypeName
END
SELECT @columnsAndhead = left(@columnsAndhead,len(@columnsAndhead)-1) +') VALUES('''
SELECT @columns = left(@columns,len(@columns)-5)
CLOSE a?
DEALLOCATE a??

exec('select '''+@columnsAndhead+'+'+@columns +'+'')'' as a from '+@tableName)