| |

VerySource

 Forgot password?
 Register
Search
View: 837|Reply: 5

Add circular data to the database (thanks)

[Copy link]

2

Threads

3

Posts

4.00

Credits

Newbie

Rank: 1

Credits
4.00

 China

Post time: 2020-2-8 18:30:01
| Show all posts |Read mode
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
Reply

Use magic Report

0

Threads

12

Posts

10.00

Credits

Newbie

Rank: 1

Credits
10.00

 China

Post time: 2020-4-1 15:15:02
| Show all posts
Where does the value of equname come from?
Reply

Use magic Report

0

Threads

49

Posts

35.00

Credits

Newbie

Rank: 1

Credits
35.00

 China

Post time: 2020-4-1 23:15:01
| Show all posts
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
Reply

Use magic Report

2

Threads

3

Posts

4.00

Credits

Newbie

Rank: 1

Credits
4.00

 China

 Author| Post time: 2020-4-3 09:00:01
| Show all posts
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?
Reply

Use magic Report

0

Threads

5

Posts

5.00

Credits

Newbie

Rank: 1

Credits
5.00

 China

Post time: 2020-4-7 21:30:02
| Show all posts
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
Reply

Use magic Report

0

Threads

126

Posts

73.00

Credits

Newbie

Rank: 1

Credits
73.00

 China

Post time: 2020-6-3 16:15:01
| Show all posts
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
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list