I found two stored procedures, which can automatically generate insertions and update stored procedures for a data table, now dedication to everyone!
insert:
Create procedure sp_GenInsert @ TableName varchar (130), @ ProcedureName varchar (130) asset nocount ondeclare @maxcol int, @ TableID intset @TableID = object_id (@TableName) select @MaxCol = max (colorder) from syscolumnswhere id = @TableIDselect 'Create Procedure ' rtrim (@ProcedureName) as type, 0 as colorder into #TempProcunionselect convert (char (35),' @ ' syscolumns.name) rtrim (systypes.name) case when rtrim (systypes.name) in ( 'binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar') Then '(' RTRIM (Convert (CHAR (4), Syscolumn.length) ')' When RTRIM SYSTYPES.NAME) Not in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar') Then '' End Case When Colorder <@maxcol the ',' When Colorder = @maxcol then '' endas type, colorderfrom syscolumnsjoin systypes on syscolumns.xtype = systypes.xtypewhere id = @TableID and systypes.name <> 'sysname'unionselect' AS ', @ maxcol 1 as colorderunionselect' INSERT INTO ' @ TableName, @ Maxcol 2 as colorderunionSelect '(', @ maxcol 3 as colorerunionse lect syscolumns.name case when colorder <@maxcol then ',' when colorder = @maxcol then '' endas type, colorder @maxcol 3 as colorderfrom syscolumnsjoin systypes on syscolumns.xtype = systypes.xtypewhere id = @TableID and systypes. Name <> 'sysname'UnionSelect') ', (2 * @maxcol) 4 As ColorderunionSelect' VALUES '
, (2 * @maxcol) 5 as colorderunionselect '(', (2 * @maxcol) 6 as colorderunionselect '@' syscolumns.name case when colorder <@maxcol then ',' when colorder = @maxcol then '' endas type, colorder (2 * @maxcol 6) as colorderfrom syscolumnsjoin systypes on syscolumns.xtype = systypes.xtypewhere id = @TableID and systypes.name <> 'sysname'unionselect') ', (3 * @maxcol) 7 As ColorderOrder by ColorderslectType from #tempproc Order by Colorder Update:
Create procedure sp_GenUpdate @ TableName varchar (130), @ PrimaryKey varchar (130), @ ProcedureName varchar (130) asset nocount ondeclare @maxcol int, @ TableID intset @TableID = object_id (@TableName) select @MaxCol = max (colorder) from syscolumnswhere id = @TableIDselect 'Create Procedure' rtrim (@ProcedureName) as type, 0 as colorder into #TempProcunionselect convert (char (35), '@' syscolumns.name) rtrim (systypes.name) case when rtrim (Systypes.name) in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar') Then '(' RTRIM (Convert (CHAR (4), Syscolumns.length)) ')' When RTrim (Systypes.name) Not in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar') Then '' end casser <@maxcol kiln , 'when colorder = @maxcol then' 'endas type, colorderfrom syscolumnsjoin systypes on syscolumns.xtype = systypes.xtypewhere id = @TableID and systypes.name <>' sysname'unionselect 'AS', @ maxcol 1 as colorderunionselect 'UPDATE ' @ TableName, @ maxcol 2 as colorderunionSelect' set ', @ maxcol 3 as colorderunionselect syscolumns.name '= @' syscolumns.name case when colorder <@maxcol then ',' when colorder = @maxcol then '' endas type, colorder @maxcol 3 as colorderfrom syscolumnsjoin systypes on syscolumns. Xtype = system Idless.xtypewhere id = @tableID and syscolumns.name <> @
