Link to home
Start Free TrialLog in
Avatar of curiouswebster
curiouswebsterFlag for United States of America

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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

"I need query an exceptions table with 14 million records and extract only records from the previous 1 year"
(...)
"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?
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of curiouswebster

ASKER

thanks
newbieweb, you shouldn't accept my comment as solution. I just asked and didn't provide any solution.
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.LogRecentRecords
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.LogRecentRecords' 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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks