求一个SQL语句...
A字段是一个INT型,范围为1-1000..只插入一条记录.这条记录的A字段自动编号为1-1000中没有被占用的数值...
举个例:如数据库中有这么几项记录,A字段分别是:1,2,3,8,9....然后再下次插入数据时,因为4没被占用,则自动分配为4...
SQL 循环插入的问题
答案:4 悬赏:0 手机版
解决时间 2021-01-04 17:12
- 提问者网友：绫月
- 2021-01-03 21:46
最佳答案
- 五星知识达人网友：长青诗
- 2021-01-03 23:23
DECLARE @iStart INT
DECLARE @iEnd INT
DECLARE @i INT
DECLARE @iCount INT
SET @iStart = 1
SET @iEnd = 1000
SET @i=@iStart
WHILE (@i <= @iEnd) BEGIN
SELECt @iCount = COUNT(1) FROM TableName WHERe A = @i
IF @iCount = 0 BEGIN
INSERT INTO TableName (A)
SELECT @i
END
SET @i=@i+1
END
DECLARE @iEnd INT
DECLARE @i INT
DECLARE @iCount INT
SET @iStart = 1
SET @iEnd = 1000
SET @i=@iStart
WHILE (@i <= @iEnd) BEGIN
SELECt @iCount = COUNT(1) FROM TableName WHERe A = @i
IF @iCount = 0 BEGIN
INSERT INTO TableName (A)
SELECT @i
END
SET @i=@i+1
END
全部回答
- 1楼网友：空山清雨
- 2021-01-04 01:21
declare @count int
declare @Tmp int
set @Tmp=1
while(@Tmp<10)
begin
select @count=count(*) from testTable where id=@Tmp
if(@count=0)
begin
SET IDENTITY_INSERT testTable ON
insert into testTable(id) values(@Tmp)
SET IDENTITY_INSERT testTable OFF
end
set @Tmp=@Tmp+1
end
- 2楼网友：迷人又混蛋
- 2021-01-03 23:48
declare @ny int,@mny int
set @ny=201101
set @mny=201212
while @ny<=@mny
begin
insert into bizyear select convert(varchar(7),cast(cast(@ny as varchar)+'01' as datetime),120)
set @ny=cast(replace(convert(varchar(7),dateadd(m,1,cast(cast(@ny as varchar)+'01' as datetime)),120),'-','') as int)
end
直接执行即可
- 3楼网友：摆渡翁
- 2021-01-03 23:41
如果你的A字段不是标示列就行在数据库中先查询出最小缺失值，使用EXISTS来做
--先生成测试数据
CREATE TABLE #Test
(
num INT PRIMARY KEY
)
INSERT #Test
SELECt 1
UNIOn ALL
SELECT 2
UNIOn ALL
SELECT 3
UNIOn ALL
SELECT 4
UNIOn ALL
SELECT 6
UNIOn ALL
SELECT 9
-----以上ID为1，2，3，4，5,7,8,那么现在插数据应该取最小缺失值6
--查询，将最小值放在一个变量里，供以后插入
DECLARE @Num int
SELECT @Num=MIN(TA.num+1)
FROM #Test AS TA
WHERe NOT EXISTS
(
SELECt *
FROM #Test AS TB
WHERe TB.num= TA.num+1
)
SELECt @Num
--如果有5，再运行上面那句将取得7
--INSERT INTO #Test SELECT 5
我要举报
如以上问答信息为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
大家都在看
推荐资讯