In the project, you need to generate an Insert script for the data in the table, and find a stored procedure from the Internet. However, if an error is reported during execution, you can modify and debug it.
Added support for SQL keywords or space-based field names, while ignoring self-growth field data.
CREATE Procedure DBGenData
@ TableName Varchar (100) -- table name
AS
DECLARE @ IsIdentity INT
DECLARE @ columnName VARCHAR (100) -- column name
DECLARE @ TypeName VARCHAR (100) -- Data Type
DECLARE @ columns VARCHAR (3000 )--
DECLARE @ columnsAndhead VARCHAR (3000 )--
SET @ columnsAndhead = 'insert' + @ tableName + '(['
SET @ columns =''
-- Obtain the names of all fields in the table.
DECLARE a CURSOR
Select columnproperty (a. id, a. name, 'isidentity ') AS IsIdentity, a. [NAME] AS ColumnName, B. [NAME] AS TypeName
FROM syscolumns a inner join policypes B ON a. xtype = B. xtype AND B. xtype = B. xusertype
WHERE a. [id] = (SELECT [id] FROM sysobjects WHERE [NAME] = @ tableName)
OPEN
Fetch next from a INTO @ IsIdentity, @ columnName, @ TypeName
WHILE @ FETCH_STATUS = 0
BEGIN
IF @ IsIdentity = 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), ''null'') + '','' +'
END
ELSE
BEGIN
SET @ columns = @ columns + ''' + ISNULL (CAST (['+ @ columnName +'] as varchar), ''null '') + ''', ''+'
END
SET @ columnsAndhead = @ columnsAndhead + ''+ @ columnName + '], ['
END
Fetch next from a INTO @ IsIdentity, @ columnName, @ TypeName
END
SELECT @ columnsAndhead = left (@ columnsAndhead, len (@ columnsAndhead)-2) + ') VALUES ('''
SELECT @ columns = left (@ columns, len (@ columns)-5)
CLOSE
DEALLOCATE
Exec ('select' + @ columnsAndhead + '+ @ columns +' + '')'' as InsertSQL from '+ @ tableName)
GO