declare @tablename VARCHAR(256) --表名称
set @tablename='DBTableName'
DECLARE @FieldName Table
(
COLUMN_NAME VARCHAR(50),
DATA_TYPE VARCHAR(50),
CHARACTER_MAXIMUM_LENGTH VARCHAR(50),
NUMERIC_PRECISION VARCHAR(50),
NUMERIC_SCALE VARCHAR(50)
)
INSERT INTO @FieldName
( COLUMN_NAME ,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE
)
select
column_name
,data_type
,CHARACTER_MAXIMUM_LENGTH
,NUMERIC_PRECISION
,NUMERIC_SCALE
from information_schema.columns where table_name = @tablename
--select * from information_schema.columns where table_name = @tablename
--select * from @FieldName
declare @COLUMN_NAME VARCHAR(50)
declare @DATA_TYPE VARCHAR(50)
declare @CHARACTER_MAXIMUM_LENGTH VARCHAR(50)
declare @NUMERIC_PRECISION VARCHAR(50)
declare @NUMERIC_SCALE VARCHAR(50)
declare @sql nvarchar(4000)
set @sql='SET ANSI_NULLS ON'+ CHAR(13)+CHAR(10)
set @sql=@sql+'Go'+CHAR(10)
set @sql=@sql+'SET QUOTED_IDENTIFIER ON'+CHAR(13)+CHAR(10)
set @sql=@sql+'Go'+CHAR(10)
set @sql=@sql+'-- ============================================='+CHAR(13)+CHAR(10)
set @sql=@sql+'-- Author:
<Author,jcstone,Name>'+CHAR(13)+CHAR(10)
set @sql=@sql+'-- Create date: <Create Date,2015-7-29,>'+CHAR(13)+CHAR(10)
set @sql=@sql+'-- Description: <Description,新增记录,>'+CHAR(13)+CHAR(10)
set @sql=@sql+'-- ============================================='+CHAR(13)+CHAR(10)
set @sql=@sql+'CREATE PROCEDURE Proc_'+ @tablename+'_Insert'
declare myCursor cursor scroll dynamic for
select * from @FieldName
declare @sqlName table(name nvarchar(50),descript nvarchar(50))
open myCursor
fetch next from myCursor into @COLUMN_NAME,@DATA_TYPE,@CHARACTER_MAXIMUM_LENGTH, @NUMERIC_PRECISION,@NUMERIC_SCALE
while (@@fetch_status=0)
begin
If @COLUMN_NAME<>'ID'
if @DATA_TYPE='nvarchar'
insert into @sqlName(name,descript) values
(' @'+@COLUMN_NAME,@DATA_TYPE+'('+@CHARACTER_MAXIMUM_LENGTH+')')
else if @DATA_TYPE='char'
insert into @sqlName(name,descript) values
(' @'+@COLUMN_NAME,@DATA_TYPE+'('+@CHARACTER_MAXIMUM_LENGTH+')')
else if @DATA_TYPE='nchar'
insert into @sqlName(name,descript) values
(' @'+@COLUMN_NAME,@DATA_TYPE+'('+@CHARACTER_MAXIMUM_LENGTH+')')
else if @DATA_TYPE='decimal'
insert into @sqlName(name,descript) values
(' @'+@COLUMN_NAME ,@DATA_TYPE+'('+@NUMERIC_PRECISION+','+@NUMERIC_SCALE+')' )
else if @DATA_TYPE='float'
insert into @sqlName(name,descript) values
(' @'+@COLUMN_NAME,@DATA_TYPE+'('+@NUMERIC_PRECISION+','+@NUMERIC_SCALE+')' )
else
insert into @sqlName(name,descript) values
(' @'+@COLUMN_NAME,@DATA_TYPE )
fetch next from myCursor into @COLUMN_NAME,@DATA_TYPE,@CHARACTER_MAXIMUM_LENGTH, @NUMERIC_PRECISION,@NUMERIC_SCALE
end
declare @Colstring varchar(500)
select @Colstring = isnull(@Colstring + ','+CHAR(13)+CHAR(10) , '' ) + isnull( Name+' '+descript, '' )
from @sqlName
set @sql=@sql+@Colstring+CHAR(13)+CHAR(10)
close myCursor
deallocate myCursor
print CHAR(13)+CHAR(10)
set @sql=@sql+' AS' + CHAR(13)+CHAR(10)
set @sql=@sql+' BEGIN'+ CHAR(13)+CHAR(10)
set @sql=@sql+' Insert into '+ @tablename + CHAR(13)+CHAR(10)
set @sql=@sql+ '(' + CHAR(13)+CHAR(10)
declare @Colstring2 varchar(500)
select @Colstring2 = isnull(@Colstring2 + ','+CHAR(13)+CHAR(10), '' ) + isnull( COLUMN_NAME , '' )
from @FieldName where COLUMN_NAME<>'ID'
set @sql=@sql+@Colstring2 + CHAR(13)+CHAR(10)
set @sql=@sql+')'+ CHAR(13)+CHAR(10)
declare @Colstring3 nvarchar(500)
select @Colstring3 = isnull(@Colstring3 + ','+CHAR(13)+CHAR(10) , ' ' ) + isnull( ' '+Name , ' ' )
from @sqlName
set @sql=@sql+' values('+@Colstring3+CHAR(13)+CHAR(10)
set @sql=@sql+' )'+CHAR(13)+CHAR(10)
set @sql=@sql+ ' END'+CHAR(13)+CHAR(10)
set @sql=@sql+ ' GO'+CHAR(13)+CHAR(10)
select @sql
print @sql
