Select, Modify & Insert Records 4

[TamedTech]

Hello Guys,

I've got a requirement I supose to duplicate set of data between a certain date and time for a particular table. I essentialy need to SELECT all records from the table as follows:

SELECT Unit_ID
       LogRef_ID
       DateTime
FROM   Message_Log
WHERE  Unit_ID = 4
AND    DateTime BETWEEN '01-10-2007 13:00:00' AND '20-10-2007 09:00:00'

I then want to reinsert those records back in to the table, but all of them with a different Unit_ID, lets say, 6 or somthing, all the other columns need to stay the same, and I dont want to delete the old records.

Does that make sense? Almost like a duplication of the records, only with a different Unit_ID. I'm not quite sure of the best process for this. I could do this using my server side code to pull the records and loop them back in with indevidual inserts, but we could be talking a fairly substantial number of records, and I'd rather handle this purely in SQL is possible as its bound to be more efficient.

I look forward to hearing your thoughts,

Rob

 

[r937]

INSERT
INTO Message_Log
SELECT [red]6[/red]
, LogRef_ID
, DateTime
FROM Message_Log
WHERE Unit_ID = 4
AND DateTime
BETWEEN '01-10-2007 13:00:00'
AND '20-10-2007 09:00:00'


r937.com | rudy.ca

 

[TamedTech]

Hey Rudy,

Thanks for the help mate, this looks to be along the right tracks, but it now looks as if its throwing an error at me.

INSERT INTO MessageLog
SELECT 
	   Device_ID
	 , LogDateTime
	 , LogClass_ID
	 , Media_ID
	 , Campaign_ID
	 , 10
  FROM MessageLog
 WHERE Unit_ID = 4
   AND LogDateTime 
       BETWEEN '26-10-2007 14:00:00'
           AND '26-10-2007 23:59:59'

When this is run it throws the following error:

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Is this to do with the order I've arranged my dd-mm-yyyy values in those date ranges?

Thanks mate, I hope you're well.

Rob

 

[gmmastros]

When hardcoding dates, it is always best to use the ISO Unseparated Date Format YYYYMMDD hh:mm:ss

       BETWEEN '20071026 14:00:00'
           AND '20071026 23:59:59'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

it's funny, george, i almost always rewrite date strings when using the sql provided by the original posters, but for some reason i forgot this time...

i guess i assumed the original poster had already run the SELECT, and that it worked

rob, i would suggest that you code the actual list of columns for the INSERT

also, don't use BETWEEN (as it stands, you might miss rows with 23:59:59.667)

use

AND LogDateTime >= '2007-10-26 14:00:00'
AND LogDateTime < '2007-10-27'


r937.com | rudy.ca

 

[WoodyGuthrie]

I think the example above is actually going to add records. Is that what you want to do? If not, you'll need to use UPDATE to SET the appropriate values to your Unit_ID column.

 

[gmmastros]

Rudy,

Did you know that your example dates are still not fool-proof?

Take a look at this:

Set DateFormat MDY

Select Month('2007-05-06') [green]-- Output = [!]5[/!][/green]

Set DateFormat DMY

Select Month('2007-05-06') [green]-- Output = [!]6[/!][/green]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TamedTech]

Haha!

Thanks guys, glad to see I've got your scrapping with one another to get the answer.

@ Woddy... this is fine, I dont want to append record, I do want to INSERT them, almost like a duplicate.

@ George and Rudy, thanks for your help guys, so let me be sure of this... I need to use the ISO format, but with the >< syntax rather than between? is that correct?

Thanks chaps,

p.s. Is there any way to 'undo' these queries if i run it and dont get the desired result?

Rob

 

[WoodyGuthrie]

p.s. Is there any way to 'undo' these queries if i run it and dont get the desired result?"


TEST....
Run in a test environment first.

 

[gmmastros]

yes. ISO Unseparated format, and between is not good.

[tt][blue] AND LogDateTime >= '20071026 14:00:00'
AND LogDateTime < '20071027'[/blue][/tt]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TamedTech]

Thanks guys,

I've just run the version of the code with all those modifications and its worked perfectly. Thanks for all your input, its really appreciated.

Rob

 

[gmmastros]

TamedTech,

I strongly encourage you do take the advice given to you by WoodyGuthrie.

Never, under any circumstances, play around with your production database. At a minimum, you should have another database with the same tables and data, to test on. It could be as simple as backing up the production database and restoring it to the same server but with a different name. This way, if you mess something up, it's ok because you've done it in your development DB instead of production.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

In case you people are bored and want to learn a little about the dates

Do You Know How Between Works With Dates?

How Are Dates Stored Internally In SQL Server?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

also, don't use BETWEEN (as it stands, you might miss rows with 23:59:59.997)

Chiming in with everyone else, even if you do get your BETWEEN statement working to get the last value for the datetime or whatever your data type, you cannot know for sure that the column will not change date data types in the future. If changed to smalldatetime, your '23:59:59.997' will break. If changed to the new SQL Server 2008 100-nanosecond precision datetime2 (if my memory is correct) your '23:59:59.997' will also break silently, missing records between '23:59:59.997' and 100 nanoseconds before midnight.

Always use >= {startdate} and < {enddate}