CODEWITHSUNDEEP
sql-servert-sql
user5087404
user5087404

Reputation:

What is wrong with this logic in T-SQL?

I want to create 100 dummy records.

Here is the code that I wrote:

create table #Dummy (ID int, Name varchar(100))

Declare @temp int = 1
declare @Name varchar(100) = ''

while @temp >= 100
begin
    set @Name = 'Name - ' + convert(varchar(100), @temp)

    insert into #Dummy 
    values (@temp, @Name) 

    set @temp = @temp + 1
end 

select * from #Dummy

but I get nothing being inserted into the table....

Upvotes: 0

Views: 64

Answers (3)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can generate numbers using row_number as below:

create table #Dummy (ID int, Name varchar(100))

Insert into #Dummy (Id, Name) 
select RowN as Id, Concat('Name - ', RowN) as Name from (
    select top (100) RowN = Row_number() over (order by (select null)) from sys.objects s1, sys.objects s2
) a

select * from #dummy

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

Here is another alternative. I really don't like loops even for creating test data. I would use a tally or numbers table for this sort of thing. I keep one handy in my system as a view like this.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally

Once you have this you can easily create any amount of test data super painlessly. Something like this.

create table #Dummy(ID int , Name varchar(100))

insert into #Dummy 
select N, 'Name - ' + CONVERT(varchar(4), N)
from cteTally t
where t.N <= 100

select *
from #Dummy

Upvotes: 4

Fuzzy
Fuzzy

Reputation: 3810

try this:

CREATE TABLE #Dummy
(ID   INT,
 Name VARCHAR(100)
);
DECLARE @temp INT= 1;
DECLARE @Name VARCHAR(100)= '';
WHILE @temp <= 100
    BEGIN
        SET @Name = 'Name - '+CONVERT(VARCHAR(100), @temp);
        INSERT INTO #Dummy
        VALUES
        (@temp,
         @Name
        );
        SET @temp += 1;
    END;
SELECT *
FROM #Dummy;

Upvotes: -1

Related Questions