I have table of employees and their ratings as shown:
我有员工表及其评级如下所示:
ID EmployeeId RatingHr RatingMgr RatingTL SelfRating
1 E1 1 1 1 1
2 E2 3 3 3 3
3 E3 5 5 5 5
4 E4 7 7 7 7
5 E5 9 9 9 9
6 E6 1 1 1 1
7 E7 3 3 3 3
8 E8 5 5 5 5
9 E9 7 7 7 7
10 E10 9 9 9 9
. . . . . .
. . . . . .
1000 E1000 9 9 9 9
I want to insert records upto 1000 like this. How should I do it.
我想像这样插入高达1000的记录。我该怎么办
2 个解决方案
#1
3
insert into YourTable(ID, EmployeeId, RatingHr, RatingMgr, RatingTL, SelfRating)
select T.N,
'E'+cast(T.N as varchar(10)),
((T.N * 2) - 1) % 10,
((T.N * 2) - 1) % 10,
((T.N * 2) - 1) % 10,
((T.N * 2) - 1) % 10
from (
select top(1000) row_number() over(order by 1/0)
from sys.all_objects as o1, sys.all_objects as o2
) as T(N)
#2
0
Try the answer below. Add the extra columns as needed -
尝试下面的答案。根据需要添加额外的列 -
declare @start int = 0
declare @end int = 1001
declare @no int = 0
create table ##emps(ID int null,
EmployeeId varchar(50) null,
RatingHr int null)
while(@start < @end)
begin
set @start = @start + 1
set @no = ((@start * 2) - 1) % 10
insert into ##emps(ID, EmployeeId, RatingHr)
values(@start, ('E' + convert(varchar(5), @start)), @no)
end
Btw, ##emps is a global temporary table. You can replace it by regular table if you need.
顺便说一下,## emps是一个全局临时表。如果需要,您可以使用常规表替换它。
#1
3
insert into YourTable(ID, EmployeeId, RatingHr, RatingMgr, RatingTL, SelfRating)
select T.N,
'E'+cast(T.N as varchar(10)),
((T.N * 2) - 1) % 10,
((T.N * 2) - 1) % 10,
((T.N * 2) - 1) % 10,
((T.N * 2) - 1) % 10
from (
select top(1000) row_number() over(order by 1/0)
from sys.all_objects as o1, sys.all_objects as o2
) as T(N)
#2
0
Try the answer below. Add the extra columns as needed -
尝试下面的答案。根据需要添加额外的列 -
declare @start int = 0
declare @end int = 1001
declare @no int = 0
create table ##emps(ID int null,
EmployeeId varchar(50) null,
RatingHr int null)
while(@start < @end)
begin
set @start = @start + 1
set @no = ((@start * 2) - 1) % 10
insert into ##emps(ID, EmployeeId, RatingHr)
values(@start, ('E' + convert(varchar(5), @start)), @no)
end
Btw, ##emps is a global temporary table. You can replace it by regular table if you need.
顺便说一下,## emps是一个全局临时表。如果需要,您可以使用常规表替换它。