CODEWITHSUNDEEP
sqlsql-server-2014
Nick
Nick

Reputation: 372

INSERT - SELECT with auto incrementing of datetime for each row inserted

I have a table with 3 keys on it, AUDIT_OPRID (varchar), AUDIT_STAMP (datetime), AUDIT_ACTN (varchar).

I am inserting rows from a select statement, using the GETDATE() function to obtain the current datetime. The values for AUDIT_OPRID and AUDIT_ACTN are the same, so the only key column I can make unique is datetime. I was hoping there was a way to auto-increment the datetime by 1 second for each row inserted, so that I do not have duplicate key issues.

I found a thread for a MySQL version of a similar situation -SQL INSERT...SELECT with auto-increment DATETIME however I'm getting syntax errors when using this in the following SQL:

DECLARE @value DATETIME
SET @value = CURRENT_TIMESTAMP

INSERT INTO PS_AUDIT_PSROLEUSR (AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN, ROLEUSER, ROLENAME, DYNAMIC_SW)
    SELECT  
        'TESTUSER', @value = @value + INTERVAL 1 SECOND, 'D', A.OPRID, D.ROLENAME, 'N'
    FROM 
        PSOPRDEFN A
    INNER JOIN 
        PS_AD_X_WALK B ON B.OPRID = A.OPRID
    INNER JOIN 
        PS_JOB C ON C.EMPLID = B.GH_AD_EMPLID
    WHERE 
        B.GH_AD_EMPLID <> ''
        AND C.ACTION = 'TER'
        AND A.ACCTLOCK = 0

The ideal result would be as follows:

AUDIT_OPRID    AUDIT_STAMP               AUDIT_ACTN    ROLEUSER         ROLENAME            DYNAMIC_SW
TESTUSER       2018-11-21 07:02:08.563   D             USER123452       GHS_PO_RECEIVER     N
TESTUSER       2018-11-21 07:02:09.563   D             USER123452       GHS_STOREROOM       N
TESTUSER       2018-11-21 07:02:10.563   D             USER123452       GH_EPRO             N

Upvotes: 0

Views: 926

Answers (1)

Karthik Ganesan
Karthik Ganesan

Reputation: 4222

You can use row_number and add that value as seconds to your timestamp value like this

DECLARE @value DATETIME
SET @value = CURRENT_TIMESTAMP

INSERT INTO PS_AUDIT_PSROLEUSR (AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN, ROLEUSER, ROLENAME, DYNAMIC_SW)
    SELECT  
        'TESTUSER', DATEADD(SECOND, ROW_NUMBER() OVER (ORDER BY A.OPRID), @value),
        'D', A.OPRID, D.ROLENAME, 'N'
    FROM 
        PSOPRDEFN A
    INNER JOIN 
        PS_AD_X_WALK B ON B.OPRID = A.OPRID
    INNER JOIN 
        PS_JOB C ON C.EMPLID = B.GH_AD_EMPLID
    WHERE 
        B.GH_AD_EMPLID <> ''
        AND C.ACTION = 'TER'
        AND A.ACCTLOCK = 0

Upvotes: 1

Related Questions