SQL: While循环中的While循环
SQL: While循环中的While循环
提问于 2017-01-17 21:38:38
我试图在父表中插入10K个名字，使用10个描述的名字，同时在后面添加数字。但是设法只写了第一个1000。我需要你的眼睛来看看我哪里失败了。我确实理解@i不是在增加，但不知道为什么。
DECLARE @TempNameTable table (ID int , Name varchar (50))
DECLARE @i int = 1,
@tempNameValue varchar(50),
@randNumber int = 1
INSERT INTO @TempNameTable VALUES
(1,'Jonas'), (2,'Petras'),(3,'Antanas')
, (4,'Stasys'), (5,'Dainius'), (6,'Giedrius')
, (7,'Mindaugas'), (8,'Povilas'), (9,'Kestutis')
, (10,'Darius')
WHILE ((SELECT COUNT(Name) FROM Parent) < 10000)
BEGIN
WHILE @i < 11
BEGIN
SET @tempNameValue = CASE
WHEN @i = 1 THEN
(SELECT Name from @TempNameTable WHERE ID = @i)
WHEN @i = 2 THEN
(SELECT Name from @TempNameTable WHERE ID = @i)
WHEN @i = 3 THEN
(SELECT Name from @TempNameTable WHERE ID = @i)
WHEN @i = 4 THEN
(SELECT Name from @TempNameTable WHERE ID = @i)
WHEN @i = 5 THEN
(SELECT Name from @TempNameTable WHERE ID = @i)
WHEN @i = 6 THEN
(SELECT Name from @TempNameTable WHERE ID = @i)
WHEN @i = 7 THEN
(SELECT Name from @TempNameTable WHERE ID = @i)
END
WHILE @randNumber < 1000
BEGIN
INSERT INTO Parent VALUES
(@tempNameValue + CAST(@randNumber as varchar(1000)))
SET @randNumber = @randNumber + 1
END
SET @i = @i + 1
END
END回答 3
Stack Overflow用户
回答已采纳
发布于 2017-01-17 21:47:21
我假设您的查询是：
DECLARE @TempNameTable table (ID int , Name varchar (50))
DECLARE @tempNameValue varchar(50),
@randNumber int = 1
INSERT INTO @TempNameTable VALUES
(1,'Jonas'), (2,'Petras'), (3,'Antanas'),
(4,'Stasys'), (5,'Dainius'), (6,'Giedrius'),
(7,'Mindaugas'), (8,'Povilas'), (9,'Kestutis'), (10,'Darius')
WHILE ((SELECT COUNT(Name) FROM Parent) < 10000)
BEGIN
SET @randNumber = 1
WHILE @randNumber <= 1000
BEGIN
INSERT INTO Parent
SELECT Name + CAST(@randNumber as varchar(1000))
FROM @TempNameTable
SET @randNumber = @randNumber + 1
END
ENDStack Overflow用户
发布于 2017-01-17 22:01:19
I'd do it like the below. Just tested and it outputs each name * 1,000 rows. It's more verbose but its obvious what it's doing and it works.
Declare @Jonas varchar(20) = 'Jonas'
Declare @Petras varchar(20) = 'Petras'
Declare @Antanas varchar(20) = 'Antanas'
Declare @Stasys varchar(20) = 'Stasys'
Declare @Dainius varchar(20) = 'Dainius'
Declare @Giedrius varchar(20) = 'Giedrius'
Declare @Mindaugas varchar(20) = 'Mindaugas'
Declare @Povilas varchar(20) = 'Povilas'
Declare @Kestutis varchar(20) = 'Kestutis'
Declare @Darius varchar(20) = 'Darius'
Declare @TempJonas varchar(20)
Declare @TempPetras varchar(20)
Declare @TempAntanas varchar(20)
Declare @TempStasys varchar(20)
Declare @TempDainius varchar(20)
Declare @TempGiedrius varchar(20)
Declare @TempMindaugas varchar(20)
Declare @TempPovilas varchar(20)
Declare @TempKestutis varchar(20)
Declare @TempDarius varchar(20)
Declare @NameIncrement int = 0
WHILE @NameIncrement <= 1000
BEGIN
Set @TempJonas = @Jonas + CONVERT(varchar(6),@NameIncrement)
Set @TempPetras = @Petras + CONVERT(varchar(6),@NameIncrement)
Set @TempAntanas = @Antanas + CONVERT(varchar(6),@NameIncrement)
Set @TempStasys = @Stasys + CONVERT(varchar(6),@NameIncrement)
Set @TempDainius = @Dainius + CONVERT(varchar(6),@NameIncrement)
Set @TempGiedrius = @Giedrius + CONVERT(varchar(6),@NameIncrement)
Set @TempMindaugas = @Mindaugas + CONVERT(varchar(6),@NameIncrement)
Set @TempPovilas = @Povilas + CONVERT(varchar(6),@NameIncrement)
Set @TempKestutis = @Kestutis + CONVERT(varchar(6),@NameIncrement)
Set @TempDarius = @Darius + CONVERT(varchar(6),@NameIncrement)
Insert Into Parent
Select @TempJonas
Insert Into Parent
Select @TempPetras
Insert Into Parent
Select @TempAntanas
Insert Into Parent
Select @TempStasys
Insert Into Parent
Select @TempDainius
Insert Into Parent
Select @TempGiedrius
Insert Into Parent
Select @TempMindaugas
Insert Into Parent
Select @TempPovilas
Insert Into Parent
Select @TempKestutis
Insert Into Parent
Select @TempDainius
Set @NameIncrement = @NameIncrement + 1
ENDStack Overflow用户
发布于 2017-01-17 22:06:00
不带循环的Set based answer：
rextester：http://rextester.com/EBM78452
declare @TempNameTable table (id int , Name varchar (50))
insert into @TempNameTable values
(1,'Jonas'), (2,'Petras'), (3,'Antanas')
, (4,'Stasys'), (5,'Dainius'), (6,'Giedrius')
, (7,'Mindaugas'), (8,'Povilas'), (9,'Kestutis')
, (10,'Darius');
if object_id('tempdb..#r') is not null drop table #r;
create table #r (n int not null primary key, r int not null);
with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, d as (
select n=row_number() over (order by (select 1))
, r=convert(int,((rand(checksum(newid())) * 10)+1))
from n as deka
cross join n as hecto
cross join n as kilo
cross join n as tenK
)
insert into #r (n, r)
select n, r from d;
--insert into Parent
select Name=Name+convert(varchar(13),n)
from #r as r
inner join @TempNameTable t on r.r=t.id它使用convert(int,((rand(checksum(newid())) * 10)+1))生成一个从1到10的随机数，并使用该随机数将临时表#r连接到@TempNameTable
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41698651
复制相关文章
相似问题
腾讯云开发者
