sql 插入行的代码，主要实现在指定行之前或者之后插入某条记录

建表

 

USE [test]
GO
/****** 对象:  Table [dbo].[InsertTest]    脚本日期: 04/30/2010 16:47:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[InsertTest](
 [LsID] [int] NOT NULL,
 [name] [varchar](100) NULL,
 [mark] [varchar](100) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

 

sql 代码 sql2005

 

-----以下是一段插入行的代码，主要实现在指定行之前或者之后插入某条记录-----
Begin Transaction
declare @ID int,@direction int
set @ID=1010 --待插入行的行号---
set @direction=0 ---插入的方向是插入行之后还是之前，0之前 1之后--

if @direction=0
 begin
  select LsID,[name],mark into T1 from InsertTest where LsID>=@ID
  delete from InsertTest where LsID>=@ID
 end
else
 begin
  select LsID,[name],mark into T1 from InsertTest where LsID>@ID
  delete from InsertTest where LsID>@ID
  set @ID=@ID+1
 end
update T1 set LsID=LsID+1
insert into InsertTest(LsID,[name],mark)values(@ID,'fg劝君终日酩酊醉','ggfh欢天喜地')
insert into InsertTest(LsID,[name],mark)select LsID,[name],mark from T1
drop table T1
commit Transaction