I have to add, 1000 records in a given table. It contains the field Name .
I have the Script, which does the bulk insertion. However, the primary key of this table is uniqueidentifier ( Guid() ) how I can do this using this approach.
Here is the script for creating with primary key 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
Use the 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 note is that if the table has more than two columns the Insert needs to specify the columns being inserted.
Something like
Insert Into (Id, Nome) Values (NewId(), 'Jack');
This is a suggestion for adding rows using a single INSERT statement 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
The table tbNumber is a permanent table, having been created previously.
-- 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: Itzik Ben-gan's T-SQL Querying
Related Documentation: