Insert 1000 records into table with uniqueidentifier as primary key

Question

I have to add, 1000 records in a given table. It contains the Field Name.

I have the Script, which does the mass insertion. However, the primary key of this table is a uniqueidentifier(Guid()) how can I do this using this approach.

Follow the script for primary key creation int

declare @id int 
select @id = 1
while @id >=1 and @id <= 1000
begin
    insert into client values(@id, 'jack' + convert(varchar(5), @id), 12)
    select @id = @id + 1
end

Answer 1

Use the function Newid()

declare @contador int 
select @contador = 1
while @contador >=1 and @contador <= 1000
begin
    insert into client values (NewId(), 'jack' + convert(varchar(5), @contador), 12)
    select @contador = @contador + 1
end

---

An important observation is that if the table has more than two columns the Insert need to specify the columns being inserted.

Something like

Insert Into (Id, Nome) Values (NewId(), 'Jack');

Answer 2

Here is a suggestion to include the lines using a single INSERT execution in the table. In general, it is more efficient.

-- código #1
INSERT into client (col1, col2, col3)
  SELECT NewId(), ('jack ' + convert(varchar(5), N.Número)), 12
    from tbNúmero as N
    where N.Número <= 1000;
go

---

Table tbNumber is a permanent table, having been previously created.

-- código #2
-- tabela auxiliar de números
CREATE TABLE tbNúmero (Número int);

declare @max int, @rc int;
set @max= 100000;
set @rc= 1;
set nocount on;
INSERT into tbNúmero(Número) values (1);
while (@rc * 2 <= @max)
   begin 
   INSERT into tbNúmero(Número) SELECT (Número + @rc) from tbNúmero;
   set @rc = @rc * 2;
   end;
INSERT into tbNúmero(Número)
   SELECT (Número + @rc) from tbNúmero where (Número + @rc) <= @max;
go

Source: Inside Microsoft SQL Server 2008: T-SQL Querying, by Itzik Ben-Gan

---

Directly related documentation:

• uniqueidentifier
• Newid()