Expanding on what has already been provided:

-- test table with data

DECLARE @test-2 TABLE (CCNumber varchar(16), CCV varchar(4) NULL);

INSERT INTO @test-2

SELECT '1234567890123456', NULL UNION ALL -- don't update this...

Okay, so we can't do it the way I was thinking of.

What we're going to do:

1. Create a temporary table with an identity column that starts at 1000001

2. Copy existing...

Except that there is already an identity...

Absolutely - I'm leaning towards the quirky update right now. The OP already has the...

Okay, I see that I didn't state that clearly enough.

In your script, it builds the test data and then...

You would... 😉

Questions:

1. Do you have a clustered index on the identity column?

2. Will the ID #'s ever need to be reset (ie changing account #, restart at 1)?

3. Is the "1"...

FYI, I've asked Jeff to poke his head in here.

Something that is really interesting in an odd way: if you build the test data first, and then separately run the...

LutzM (11/16/2010)

---

If what you did over there would be considered being harsh, I'd have to stop posting!!

The effort you put in...

Okay, that 31million row test table is making testing take too long.

Using:

SELECT @NumAccounts = 1000,

@DateSpan = 400

When doing...

Welcome.

Craig - I'm testing... I'll get back later.

I'm using:

SELECT @NumAccounts = 10000,

@DateSpan = 4000

Test table total: 31,986,801 rows

FYI:...

LutzM (11/16/2010)

---

WayneS (11/16/2010)

---

One thing that is missing from the query is ordering. Do you want the column names ordered alphabetically, or by their position in the table, or is random...

Note that you can accomplish this with a CTE:

;WITH CTE AS

(

SELECT [CLIENT_NUMBER],

[CLIENT_SURNAME],

[CLIENT_GIVEN_NAME],

...

However, you are allowed to have the same INDEX name across multiple tables. If you're getting a duplicate index name error, then an index of that name already exists for...