Shulou(Shulou.com)05/31 Report--

How to use SQL statement to insert a new field in front of the specified field, I believe that many inexperienced people are at a loss about this. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Create proc addcolumn@tablename varchar (30),-- Table name @ colname varchar (30),-- column name to add @ coltype varchar (100) -- column type to be added @ colid int-- add to column asdeclare @ colid_max intdeclare @ sql varchar (1000)-- dynamic sql statement-if not exists (select 1 from sysobjectswhere name = @ tablename and xtype ='u') beginraiserror 20001 'does not have this table' return-1end -if exists (select 1 from syscolumnswhere id = object_id (@ tablename) and name = @ colname) beginraiserror 20002 'this table already has this column!' Return-1end

Make sure that the colid of the table is continuous select @ colid_max = max (colid) from syscolumns where id=object_id (@ tablename) if @ colid > @ colid_max or @ colid

< 1set @colid = @colid + 1--------------------------------------------------set @sql = 'alter table '+@tablename+' add '+@colname+' '+@coltypeexec(@sql)select @colid_max = colidfrom syscolumns where id = object_id(@tablename) and name = @colnameif @@rowcount 1beginraiserror 20003 '加一个新列不成功，请检查你的列类型是否正确'return -1end 打开修改系统表的开关EXEC sp_configure 'allow updates',1 RECONFIGURE WITH OVERRIDE--将新列列号暂置为-1set @sql = 'update syscolumnsset colid = -1where id = object_id('''+@tablename+''')and colid = '+cast(@colid_max as varchar(10))exec(@sql) 将其他列的列号加1set @sql = 'update syscolumnsset colid = colid + 1where id = object_id('''+@tablename+''')and colid >

='+ cast (@ colid as varchar (10)) exec (@ sql)

Reset the new column number set @ sql = 'update syscolumnsset colid =' + cast (@ colid as varchar (10)) + 'where id = object_id (''+ @ tablename+''') and name =''+ @ colname + 'exec (@ sql)

Turn off the switch EXEC sp_configure 'allow updates',0 RECONFIGURE WITH OVERRIDEgo that modifies the system table to call the method: exec addcolumn' table name', 'new column name', 'new column type', add to a few places such as: exec addcolumn 'test','id2','char (10)', 2 means to add the column id2 to the second position of table test, the type is char (10).

After reading the above, have you learned how to insert a new field in front of a specified field with SQL statements? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.