insert duplicate records with new timestamp

  • how can i insert records from a transaction table into a transaction table with new date/time. from the table below ID is identity column, i want to do the following insert with a new date and incremental time of the transaction day.

    insert into TransactionEntry

    select * FROM [dbo].[TransactionEntry]

    where TransactionTime>'2019-07-04' and TransactionTime<'2019-07-05'

     

    SELECT [Commission]
    ,[Cost]
    ,[FullPrice]
    ,[StoreID]
    ,[ID]
    ,[TransactionNumber]
    ,[ItemID]
    ,[Price]
    ,[PriceSource]
    ,[Quantity]
    ,[SalesRepID]
    ,[Taxable]
    ,[DetailID]
    ,[Comment]
    ,[DBTimeStamp]
    ,[DiscountReasonCodeID]
    ,[ReturnReasonCodeID]
    ,[TaxChangeReasonCodeID]
    ,[SalesTax]
    ,[QuantityDiscountID]
    ,[ItemType]
    ,[ComputedQuantity]
    ,[TransactionTime]
    ,[IsAddMoney]
    ,[VoucherID]
    FROM [dbo].[TransactionEntry]
  • Please clarify what you mean by this:

    incremental time of the transaction day.

    What is that, and how does it differ from 'time'?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • i would like to insert transactions at different timestamps of the day randomly , something like

    transaction1 - 04/22/2024 9.55am

    transaction2 - 04/22/2024 9.59am

    transaction3 - 04/22/2024 10.11am

  • As a starting point - to insert into a table that has an IDENTITY column you MUST specify the column list excluding the IDENTITY column and cannot use SELECT * in the select statement.

     INSERT INTO your_table (list of columns here)
    SELECT {list of columns here}
    FROM your_table
    WHERE ...

    In the list of columns from the SELECT - you then update any columns you want to change from the 'source' value.  For example:

     INSERT INTO your_table ( ..., TransactionTime, ...)
    SELECT ...
    , NewTransactionTime = DATEADD(DAY, 1, TransactionTime)
    ...
    FROM your_table
    WHERE ...

    Final Note:

    WHERE TransactionTime > '2019-07-04'

    The above will be implicitly converted to this WHERE TransactionTime > '2019-07-04 00:00:00.000' and that will exclude all data that has that time.  Generally, you want to use greater than or equal to the starting date/time - and less than midnight of the day following you end range.  To include all of the data from the 4th you would use: WHERE TransactionTime >= '2019-07-04' AND TransactionTime < '2019-07-05'.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Tara-1044200 wrote:

    i would like to insert transactions at different timestamps of the day randomly , something like

    transaction1 - 04/22/2024 9.55am

    transaction2 - 04/22/2024 9.59am

    transaction3 - 04/22/2024 10.11am

    Perhaps 'randomly' is not wholly accurate in this case? Are you looking for uniqueness too? Also, is there any ordering in your base data which should be preserved when assigning these 'random' times (t1 comes before t2, therefore 'random' time 1 comes before 'random' time 2)?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply