|
Resources » Code Snippets » SQL
Create Insert Stored Procedure Automatically in MS SQL
If you wanted to create insert procedure automatically then this code will help you. You just need to pass the Table name as parameter to this stored procedure. Learn how to Create Insert Stored Procedure Automatically in MS SQL.
|
How to Create Insert Stored Procedure Automatically in MS SQL
Here I am going to explain you how to create insert stored procedure automatically by passing the table name as parameter.
Use below code to create the stored procedure. If you get any error then check the Data type of the fields.
CREATE Proc sp_InsertProc
(
@TableName varchar(50)
)
as
begin
declare @Count int,@i int
declare @Str varchar(max),@sql varchar(max),@sql1 varchar(max)
declare @ColName varchar(max),@ConCat varchar(max)
declare @ColNameTab varchar(max),@ConCatTab varchar(max),@chTableName varchar(max)
set @Count=(select count(ordinal_position) from information_schema.columns where table_name=''+@TableName+'')
set @i=1
set @sql=''
set @ConCat=''
set @ConCatTab=''
set @chTableName='sp_'+@TableName+'_Insert'
if exists(select * from sysobjects where xtype='p' and Name=''+@chTableName+'')
begin
print 'Insert procedure already exists'
End
else
begin
while (@i<=@Count)
begin
set @Str=(select '@'+convert(varchar(max),Column_Name) +' '+
case when data_type='nvarchar' then 'nvarchar('+convert(varchar(10),character_maximum_length)+') ,'
when data_type='varchar' then 'varchar('+convert(varchar(10),character_maximum_length)+') ,'
when data_type='varchar2' then 'varchar2('+convert(varchar(10),character_maximum_length)+') ,'
when data_type='char' then 'char('+convert(varchar(10),character_maximum_length)+') ,'
when data_type='numeric' then 'numeric('+convert(varchar(10),numeric_precision)+','+convert(varchar(10),numeric_scale)+') ,'
when data_type='decimal' then 'decimal('+convert(varchar(10),numeric_precision)+','+convert(varchar(10),numeric_scale)+') ,'
when data_type='float' then convert(varchar(10),'float ,')
when data_type='int' then convert(varchar(10),'int ,')
when data_type='bit' then convert(varchar(10),'bit ,')
when data_type='image' then convert(varchar(10),'image ,')
when data_type='datetime' then convert(varchar(10),'datetime ,')
End as ColumnName
from information_schema.columns where table_name=''+@tableName+'' and ordinal_position=@i)
set @sql=@sql+@Str
set @ColName=(select Column_Name +',' from information_schema.columns where table_name=''+@tableName+'' and ordinal_position=@i)
set @ConCat=@ConCat+@ColName
set @ColNameTab=(select '@'+Column_Name +',' from information_schema.columns where table_name=''+@tableName+'' and ordinal_position=@i)
set @ConCatTab=@ConCatTab+@ColNameTab
set @i=@i+1
end
set @sql1='Create proc sp_'+@tableName+'_Insert('+left(@sql,len(@sql)-1)+')
as
begin
insert into '+@TableName+'('+left(@ConCat,len(@ConCat)-1)+') values('+left(@ConCatTab,len(@ConCatTab)-1)+')
End'
exec(@sql1)
End
End
After that you execute procedure like below,
exec sp_InsertProc 'TableName'
|
Did you like this resource? Share it with your friends and show your love!
|
|
|
Follow us on Twitter: https://twitter.com/dotnetspider
|
|