能将已有记录的表自动生成INSERT INTO 脚本的存储过程。(0分)

试试吧，如果要做初始化脚本的话，它可少不了哦，欢迎大虾们指正。呵呵

----------以下为脚本----------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[生成插入脚本]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [生成插入脚本]
GO

create proc dbo.生成插入脚本
@p表名 nvarchar(255)
as


set nocount on


DECLARE
@lColCouts INT,
@ltmp int,
@lSql nvarchar(4000),
@lsql2 nvarchar(4000),
@colname nvarchar(255),
@colvalues nvarchar(255),
@lsqlall nvarchar(4000)
create table #ColsTable
([id] [int] IDENTITY (1, 1) NOT NULL ,
[colname] [varchar] (50) NULL ,
[colvalues] [varchar](100) null
) ON [PRIMARY]

insert into #ColsTable (colname,colvalues)
select '[' + A.name + ']' as colname,
colvalues=case
when B.name in (
'ntext','nvarchar','nchar','text','varchar',
'char','smalldatetime','datetime')
then '''''''+ISNULL(CAST([' + A.name + '] as VARCHAR(4000)),''null'')+'''''''
else
'''+ISNULL(CAST([' + A.name + '] as VARCHAR(4000)),''null'')+'''
end

from syscolumns A,
systypes B
where
B.name in (
'ntext','nvarchar','nchar','text','varchar',
'char','smalldatetime','datetime','real','float','money',
'smallmoney','bigint','int','numeric','tinyint','decimal',
'bit'
) and
B.xtype=A.xtype and
A.status<>128 and
A.id=object_id(@p表名) order by A.colid


--select * from #tmp where colname is not null

--drop table #tmp
select @lColCouts= count(colname) from #ColsTable

select @lSql=''
SELECT @lsql2=''
select @ltmp=1
while @ltmp<=@lColCouts
begin
select @colname = colname from #ColsTable where [id]=@ltmp
select @colvalues = colvalues from #ColsTable where [id]=@ltmp
if @ltmp=@lColCouts
begin
select @lSql=@lSql+ @colname
select @lsql2=@lsql2+ @colvalues
end
else
begin
select @lSql=@lSql+ @colname + ','
select @lsql2=@lsql2+ @colvalues + ','
end

select @ltmp=@ltmp+1
end
--select len(@lSql)


--select @lsqlall ='select ''insert into address (' + @lSql + ') values(''+' + @lsql2 + '+'')'''
--select @lsqlall=@lsqlall + ' from ' + @p表名
create table #tmp
(
sql varchar(4000) not null
)
ON [PRIMARY]
exec ('insert into #tmp(sql) select ''insert into '+ @p表名 +' (' + @lSql + ') values('+ @lsql2 + ')'''+ ' as sql from' + @p表名)
--update #tmp set sql= replace(sql,'''null''','null')
select count(*) as 初始化数据 from #tmp
select replace(sql,'''null''','null') as 初始化脚本 from #tmp
drop table #tmp
drop table #ColsTable
GO


exec 生成插入脚本 '[address]'

学习！
谢谢 ：）

大哥：看来你的帖子，受益很多请到下面两个地方
领分，表达我的谢意！！！
http://www.delphibbs.com/delphibbs/dispq.asp?lid=1870106

http://www.delphibbs.com/delphibbs/dispq.asp?lid=1234407