--Handling gender randomization
Select (Case if round (rand () *10,0) >5 Then ' male ' else ' end '),
--random within the processing time frame
Select DATEADD (Dd,round (DateDiff (DD, ' 1992-01-01 ', ' 1995-01-01 ') *rand (), 0), ' 1992-01-01 ')
--Add foreign keys
ALTER TABLE [dbo]. [Studnetinfo] With CHECK ADD CONSTRAINT [Fk_studnetinfo_studentclass] FOREIGN KEY ([classId])
REFERENCES [dbo]. [StudentClass] ([ClassID])
--Delete foreign keys
ALTER TABLE Studnetinfo DROP constraint [Fk_studnetinfo_studentclass]
TRUNCATE TABLE Studnetinfo
--Query all associated constraints
EXEC sp_helpconstraint Studnetinf
--cursors handle complex insertions that are restricted by other tables
DECLARE @i int, @j int [email protected] is the number of records @classsum Traverse, control while loop; @j record the sequence number of all loops
Set @j=0
Declare cur_temp cursor FOR select classid,classsum from StudentClass
Open Cur_temp
DECLARE @classid varchar (5)
DECLARE @classsum int
FETCH NEXT from Cur_temp to @classid, @classsum
While @ @FETCH_STATUS =0
BEGIN
--print @classid--class + year + 3-bit sort number 0012015001 9-bit
Set @i=0
Print @classsum
while (@i< @classsum)
Begin
Insert into Studnetinfo (Stuid,stuname,classid,sex,rx_time,bron)
--processing of 3-bit sort numbers
Select @classid + ' +right ' (' xx ' +convert (varchar (3), @j), 3) as study number,
' Humin ' +convert (varchar (5), @j) as name,
@classid as class number,
(Case if Round (rand () *10,0) >5 Then ' men ' Else ' "end) as gender,
Convert (Datetime,left (year (GETDATE ()), 4) + ' -09-01 ') as admission time,
DATEADD (Dd,round (DateDiff (DD, ' 1992-01-01 ', ' 1995-01-01 ') *rand (), 0), ' 1992-01-01 ') as birth time
--from StudentClass
Set @[email protected]+1
Set @[email protected]+1
End
FETCH NEXT from Cur_temp to @classid, @classsum
End
Close Cur_temp
Deallocate cur_temp
Print @i
Print @j
--left (rand ()) Handles rounding
--round (rand (), 0) rounding value
SQL Server inserts random numbers