Add circular data to the database (thanks)

kaga881110

There is a table with the following structure:
faultbit faultname eqname
0 overload 1a
1 Deviation 1a
2 1a
3 1a
4 1a
5 1a
6 1a
7 1a
0 overload 2a
1 Deviation 2a
2 2a
3 2a
4 2a
5 2a
6 2a
7 2a
.... By analogy, there are 1000 different equname names, each equname corresponds to the same faultname value, how should I write a loop sql statement to quickly complete this table, and each equname corresponds to the faultbit value 0 to 8. Thank you

116657396

Where does the value of equname come from?

michealhenry

Where did eqname come from?
From another table?
If it is obtained from the table teqname

select faultbit, faultname, eqname
into mytable
from (select eqname from teqname) a,
(select 0 as faultbit, overload as faultname union
select 1, deviation from union
select 2, '' union
select 3, '' union
select 4, '' union
select 5, '' union
select 6, '' union
select 7, '') b

-If obtained from other places, or if there is a certain pattern
Then

create table mytable
(
faultbit int,
faultname varchar (10),
eqname varchar (10)
)

declare @eqname
set @eqname = 'value'-assignment
while (condition)
begin
insert inot mytable (faultbit, faultname, eqname)
select faultbit, faultname, @eqname as eqname
from
(select 0 as faultbit, overload as faultname union
select 1, deviation from union
select 2, '' union
select 3, '' union
select 4, '' union
select 5, '' union
select 6, '' union
select 7, '') b

-Here is a statement to assign a new value to @eqname
end

kaga881110

Added: Equiname is obtained from another excel table, a total of 1000, how to insert these 1000 * 8 data fast? The structure of the table already exists! Cold flute, can you make the cycle clearer?

zalinzhang

Declare @i int, @ ii int, @ faultname char (10), @ equname char (10)
set @ i = 1
Set @ ii = 1

while @ii <1001
begin

set @ equname = convert (varchar, @ ii) + 'a'
print @equname

while @i <9
begin
 IF @ i = 1 SET @ faultname = 'aaa'
 IF @ i = 2 SET @ faultname = 'bbb'
 IF @ i = 3 SET @ faultname = 'ccc'
 IF @ i = 4 SET @ faultname = 'ddd'
 IF @ i = 5 SET @ faultname = 'eee'
 IF @ i = 6 SET @ faultname = 'fff'
 IF @ i = 7 SET @ faultname = 'ggg'
 IF @ i = 8 SET @ faultname = 'hhh'

  insert into fault (faultbit, faultname, equname) values ​​(@ i, @ faultname, @ equname)
  set @i = @i +1
end
if @ i = 9 set @ i = 1

set @ ii = @ ii + 1
end

wgfxman

insert into target table (faultbit, faultname, eqname)
    select 0, column 1, eqname FROM excel sheet
UNION ALL select 1, column 2, eqname FROM excel sheet
UNION ALL select 2, column 3, eqname FROM excel sheet
UNION ALL select 3, column 4, eqname FROM excel sheet
UNION ALL select 4, column 5, eqname FROM excel sheet
UNION ALL select 5, column 6, eqname FROM excel sheet
UNION ALL select 6, column 7, eqname FROM excel sheet
UNION ALL select 7, column 8, eqname FROM excel sheet
UNION ALL select 8, column 9, eqname FROM excel sheet