insert duplicate records with new timestamp

Question

insert duplicate records with new timestamp

SSCoach

Points: 15951

April 22, 2024 at 5:42 pm

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]

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Please clarify what you mean by this:

incremental time of the transaction day.

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

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)?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin