question

ThouheedB-8933 avatar image
0 Votes"
ThouheedB-8933 asked AdrianMartinez-0787 answered

How to insert random bluk records in a table as per date

I have a table called T_SMS_NOTIFICATIONS in that i want to insert huge records as per date like 80 records for october 8 2020, 920 records for october 9 2020.... 25 records for october 31st.

to do that i have created a table called test which has records like

100835-image.png




The below code i have returned to load the records based on date:

DECLARE @NOTIFICATION_ID VARCHAR(36)='5eda1ff6-bf89-11eb-8f1c-8f99b4f0f32b',
@PROFILE_ID VARCHAR(36)='a90f91bd-bd38-11eb-b4d1-e1fb2b121933',
@SOLUTION_TYPE_ID VARCHAR(36)='5c3fb969-bd2d-11eb-b4d1-09b63d5a372d'
DECLARE ROW_COUNT CURSOR
FOR SELECT COUNT FROM TEST
DECLARE @DATE DATETIME='2020-10-11 15:40:59.807',
@COUNT BIGINT=3063,
@CNT INT=1,
@NUMBER_OF_RECORDS INT

OPEN ROW_COUNT
FETCH NEXT FROM ROW_COUNT INTO @NUMBER_OF_RECORDS

WHILE @@FETCH_STATUS = 0
BEGIN

 WHILE(@CNT<=@NUMBER_OF_RECORDS)
     BEGIN
         INSERT INTO MASTER.[T_SMS_NOTIFICATIONS](    [NOTIFICATION_ID]
                                         ,[BATCH_ID]
                                         ,[RECIEVER_PHNUM]
                                         ,[SOLUTION_NAME]
                                         ,[RESPONSE_CODE]
                                         ,[RESPONSE_DESCRIPTION]
                                         ,[CREATED_BY]
                                         ,[CREATED_DT]
                                         ,[LAST_UPDATED_BY]
                                         ,[LAST_UPDATED_DT]
                                         ,[CHANNEL_ID]
                                         ,[PROFILE_ID]
                                         ,[SOLUTION_TYPE_ID]
                                         ,[SMS_PROVIDER_ID]
                                         ,[SMS_ID]
                                         ,[RESPONSE_TIME])
          VALUES(    CONCAT(SUBSTRING(@NOTIFICATION_ID,1,LEN(@NOTIFICATION_ID)-LEN(@COUNT)),@COUNT),
              '153517152984',
              'oUmkYpw6PM4CdiW5b013Fw==',
              'admin',
              200,
              'TTLExpiredException: The message expired and was dead lettered.',
              'ssx==',
              @DATE,
              'ssx==',
              @DATE,
              '6485a2b4-8f61-11ea-bc55-0242ac130003',
              @PROFILE_ID,
              @SOLUTION_TYPE_ID,
              '3e7bc5b6-01c4-46d7-ad23-bfa74dc10129',
              1,
              1)
                 SET @CNT=@CNT+1
                 SET @COUNT=@COUNT+1
     END
 FETCH NEXT from ROW_COUNT INTO @NUMBER_OF_RECORDS         
     SET @DATE=DATEADD(DD,1,@DATE)

END
DEALLOCATE ROW_COUNT

Since i used nested while loop cursor only takes first value as @number_of_records value and fetch next from row_count into @number_of_records are not working.

I need a script to insert random number of records into a table for different date.

sql-server-transact-sql
image.png (3.0 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any update?

0 Votes 0 ·

Do you want in the same script, insert random number of records... for a specific day, range of days?

I understand that your example of the TEST table was for found an option for do this, but isn't mandatory, I'm right?

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered EchoLiu-msft edited

Add SET @CNT = 1 before the second WHILE line.

Do you need something more?



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, I think he also needs to get the value of @DATE from his table.

0 Votes 0 ·
AdrianMartinez-0787 avatar image
0 Votes"
AdrianMartinez-0787 answered

Add SET @CNT = 1 before the second WHILE line.
I also agree with that because I have tried it...
It works until you run out of credits.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.