CODEWITHSUNDEEP
sqlsql-server
goalie7960
goalie7960

Reputation: 873

SQL Server insert multiple records with same datetime

I have a table that has a datetime column that I wish to insert multiple records into at the same time. It is necessary that the datetime is exactly the same so I can late compare which records were inserted together. Is it sufficient to do something like this:

INSERT INTO Table(Date, ID, etc...)
VALUES (GETDATE(), ...)

Or should I declare a variable earlier in the stored procedure to hold the current time and just use that as the value to insert.

I have tested and it seems like the first method works fine, I just want to be 100% certain.

Edit: I think the question is better stated as "How often does GETDATE() get executed in a stored procedure?"

Upvotes: 2

Views: 4148

Answers (5)

Doug
Doug

Reputation: 6442

Store the current datetime in a variable and then use it in all the insert queries.

declare @now datetime
set @now = getdate()

insert into table (datecolumn) values (@now)

UPDATE: as noted by Joe in the accepted answer, GETDATE() is evaluated once per query, making unnecessary to capture its value in a variable. But doing so make the intention more obvious and improve mainteinability, IMHO.

Upvotes: 2

Nicholas Carey
Nicholas Carey

Reputation: 74267

Even though getdate() is executed once per query, my rule in stored procedures is to get the current date/time at the beginning of the stored procedure execution and save it in a datetime variable (call it @dtNow). That gives me a consistent notion of "nowness" for the entire execution of the stored procedure.

Having a stored procedure dependent upon the current date execute such that the period of execution spans end-of-day will teach you the benefit of this approach. Avoids much confusion whilst trying to figure out why the stored procedure failed in mysterious ways.

Also helps to establish similar variables to mark start- and end-of-day for the date in question:

declare
  @dtNow datetime ,
  @dtFrom datetime ,
  @dtThru datetime

set @dtNow  = current_timestamp
set @dtFrom = convert(datetime,convert(varchar,@dtNow,112),112)
set @dtThru = dateadd(ms,-3,dateadd(day,1,@dtFrom))

This technique lets you filter on datetime values to collect stuff for the current day (which may not necessarily actually be the date of execution):

select *
from foo
where foo.dtSold between @dtFrom and @dtThru

This is, of course, all dependent the actual requirements that bear upon the problem at hand.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

GETDATE() is evaluated once per query. There's no need to declare a variable.

Upvotes: 7

Randy
Randy

Reputation: 16677

the date will easily switch to a new millisecond or hundredth - depending on your database.

you should store a single variable and use it repeatedly.

Upvotes: 0

James Hill
James Hill

Reputation: 61802

Yes, declare a variable to hold the current time if you want to use that approach. You could also create a parent record and assign a single datetime to it, and then write the parent ID into the child records. Relying on a DateTime stamp will probably work OK, but it seems a little dirty.

Upvotes: 3

Related Questions