curiouswebster
asked on
T-SQL: How to extract records into a new table
I need query an exceptions table with 14 million records and extract only records from the previous 1 year. There is a datetime field which contains the creation date for that row.
How do I write the query that inserts only records that are newer than 1 years old?
Thanks
How do I write the query that inserts only records that are newer than 1 years old?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
newbieweb, you shouldn't accept my comment as solution. I just asked and didn't provide any solution.
ASKER
I asked it be re-opened. Also, I do have a conflict
I created the exact clone of the Log table but used a unique names for the table and constrain.
INSERT INTO Logging.dbo.LogRecentRecor ds
SELECT * FROM Logging.dbo.Log L
WHERE L.Timestamp > DATEDIFF(year, -1, GETDATE())
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'Logging.dbo.LogRecentReco rds' can only be specified when a column list is used and IDENTITY_INSERT is ON.
So, it's having trouble inserting the primary key. It would be better to preserve the exact LogID in the event I ever need to correlate those records.
The LogRecentRecords clones table will never need to be used for any purpose other than me executing queries against the data, so if it has IDENTITY_INSERT set to OFF, that's fine.
I created the exact clone of the Log table but used a unique names for the table and constrain.
INSERT INTO Logging.dbo.LogRecentRecor
SELECT * FROM Logging.dbo.Log L
WHERE L.Timestamp > DATEDIFF(year, -1, GETDATE())
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table 'Logging.dbo.LogRecentReco
So, it's having trouble inserting the primary key. It would be better to preserve the exact LogID in the event I ever need to correlate those records.
The LogRecentRecords clones table will never need to be used for any purpose other than me executing queries against the data, so if it has IDENTITY_INSERT set to OFF, that's fine.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
(...)
"How do I write the query that inserts only records that are newer than 1 years old?"
After all what do you need? To retrieve or to insert records?