Ah... got it... sorry, Remi... thought you lost your mind for a minute   I should have known better.

Bill,

Slight "lull" in the action here so I thought I'd check... was your question answered or did you have something else in mind?  And, I didn't get the chance to...

Heh... thanks for the feedback, David.

Substitute your date column or date variable for GETDATE() in the following...

 SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

You too... Thanks, Marcus...

Heh, too much "potty" time, James? (just kidding)  Me?  The shower can wait, I'm headed for a beer!

Sorry David, didn't list all of the switches you needed 'cause I thought you might already know them...  You'll need the following switches, as well...

-s"" -w256 -n

Replace the "256" with...

Bill,

Thanks for posting code that actually creates some data... usually saves a lot of time for respondents and we appreciated it a LOT!

I always wanted to know how fast that method ran

--=================================================================================================
--      Prepare a test table
--=================================================================================================

--===== If the test table exists, drop
     IF OBJECT_ID('TempDB.dbo.#TestData') IS...

O-ki-do-ki

DBCC UPDATEUSAGE(0)

--===== "Space Used on Sterioids"
     -- Created by Jeff Moden
     -- If "UnusedKB" is negative, it's likely you need to run DBCC...

Your handle says "SQLDBA" so I'm thinking that you know where Books Online is...  lookup DATEADD, Insert, Delete, and WHERE NOT IN.

Doesn't help the processing speed at all but does take less size for the dynamic SQL, the WHERE clause should be constructed more like the following...

FROM RecurrentOutcomeParticipants rop

WHERE...

use occ_archives

insert into asqcrr.occ_archives.dbo.hvcfids

select * from asqcrr.occ_archives.dbo.hvcfids as dest

Where not exists

(select workitemid

from asqcrr.occ_archives.dbo.hvcfids as source

where source.workitemid = dest.workitemid)

Way too much info

Yes, and no... if you use a format file for repetative imports, the answer is yes...  but the data in this example won't support your method or my method because the...