Or simply use a Tally table:

DECLARE @StartDatedate = '20130115',

@EndDatedate = '20130307';

WITH E1(N) AS (

...

Import/Export wizard should perform better than inserts from openrowset.

I understand that you already have the scripts to migrate but the wizard will create the SSIS package automatically and will preform...

I just noticed the additional conditions on your WHERE clause that will always evaluate to false

OR 1 = 0 OR 6 = 0

Why do you even have them?...

It's basically the same as using the subquery but using another CTE.

WITH tmpTable AS (

SELECT DISTINCT *,

ROW_NUMBER() OVER ( ORDER BY InventorySys ) AS RowNumber

FROM...

Are you really suggesting to read the table 3 times instead of once?

Something like this?

WITH CTE AS(

SELECT *,

RANK() OVER(PARTITION BY cStudentID, CourseID ORDER BY CASE Termcode WHEN 'FG1' THEN 1 WHEN 'Q1' THEN 2 ELSE 3 END) rn

FROM #TEMP

)

SELECT *

FROM CTE...

What exactly did you try? I'd rather help you to correct it than give you the whole answer to your problem. That way you can identify what you did wrong.

You already have a post about this issue and I gave you a static example and a link with an article to make it dynamic. I could easily give you...

You should have a table for Orders and another one for Orders details.

I'm not sure if I understand your requirement, but if you want to have a PK by month, you could create an additional CHECK constraint to validate for the first...

I'm not sure if SQL Server will be efficient on managing this, but it seems that this code should do the work. Note that I had to cast the text...

For explanation on the code that Keith posted, read the following article and post back if you have any more questions.

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

Try removing DEPARTID from your GROUP BY clause.

I agree with Steve, you need to review why do you have bad data and correct it....

Could you post DDL, sample data in the form of INSERT INTO statements and expected results based on that sample data.

I'm not sure if you have the best design idea...

I can help you with the basic code, however, you need to work on doing it dynamic as it involves more work and time which I don't have.

For dynamic code...