March 14, 2012 at 7:52 am
beginnew2012 (3/14/2012)
Cadavre, I don't want the table to be a temporary table. I want an actual table, what changes should I apply?
Change all references to #testEnvironment into whatever table name you want it to be.
e.g.
SET NOCOUNT ON;
IF object_id('tempdb..#testNames') IS NOT NULL
BEGIN
DROP TABLE #testNames;
END;
WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),
t2(N) AS (SELECT 1 FROM t1 x, t1 y),
t3(N) AS (SELECT 1 FROM t2 x, t2 y),
Tally(N) AS (SELECT TOP 98 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t3 x, t3 y),
Tally2(N) AS (SELECT TOP 5 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM t3 x, t3 y),
Combinations(N) AS (SELECT DISTINCT LTRIM(RTRIM(RTRIM(SUBSTRING(poss,a.N,2)) + SUBSTRING(vowels,b.N,1)))
FROM Tally a
CROSS JOIN Tally2 b
CROSS APPLY (SELECT 'B C D F G H J K L M N P R S T V W Z SCSKKNSNSPSTBLCLFLGLPLSLBRCRDRFRGRPRTRVRSHSMGHCHPHRHWHBWCWSWTW') d(poss)
CROSS APPLY (SELECT 'AEIOU') e(vowels))
SELECT IDENTITY(INT,1,1) AS ID, a.N + b.N AS N
INTO #testNames
FROM Combinations a
CROSS JOIN Combinations b;
IF object_id('tempdb..#testNames2') IS NOT NULL
BEGIN
DROP TABLE #testNames2;
END;
SELECT IDENTITY(INT,1,1) AS ID, firstName, secondName
INTO #testNames2
FROM (SELECT firstName, secondName
FROM (SELECT TOP 1000 --1000 * 1000 = 1,000,000 rows
N AS firstName
FROM #testNames
ORDER BY NEWID()) a
CROSS JOIN (SELECT TOP 1000 --1000 * 1000 = 1,000,000 rows
N AS secondName
FROM #testNames
ORDER BY NEWID()) b) innerQ;
IF object_id('tempdb..#testNames3') IS NOT NULL
BEGIN
DROP TABLE #testNames3;
END;
SELECT firstName, secondName,
firstName + '.' + secondName + '@fake.com' AS eMail,
CAST((ABS(CHECKSUM(NEWID())) % 250) + 1 AS VARCHAR(3)) + ' ' AS mailAddress,
(ABS(CHECKSUM(NEWID())) % 152100) + 1 AS jID,
IDENTITY(INT,1,1) AS ID
INTO #testNames3
FROM #testNames2
IF object_id('testEnvironment') IS NOT NULL
BEGIN
DROP TABLE testEnvironment;
END;
SELECT IDENTITY(INT,1,1) AS ID, firstName, secondName, eMail,
mailAddress + b.N + b.N AS mailAddress
INTO testEnvironment
FROM #testNames3 a
INNER JOIN #testNames b ON a.jID = b.ID;
--CLEAN UP USELESS TABLES
DROP TABLE #testNames;
DROP TABLE #testNames2;
DROP TABLE #testNames3;
March 14, 2012 at 8:05 am
Thanks I will give it a try 🙂
March 14, 2012 at 10:26 am
Excellent solution ! I like your SQL code !
March 14, 2012 at 11:39 am
I'm sorry but I don't see any create table statements?
I want my table to be stored in a separate database, let's call it myDB.
This code is very complex to me to understand, but can it be modified to create 100 million rows? 10000 * 10000 ?
March 14, 2012 at 12:42 pm
beginnew2012 (3/14/2012)
I'm sorry but I don't see any create table statements?I want my table to be stored in a separate database, let's call it myDB.
This code is very complex to me to understand, but can it be modified to create 100 million rows? 10000 * 10000 ?
You could modify it to do 100 million rows, but it would explode your tempDB (not literally, but it'd be massive). At most, it could do 23,134,410,000 rows (152,100 * 152,100).
There is no create table statement. Instead I've used SELECT INTO.
This part of the code puts your rows into a table called testEnvironment: -
IF object_id('testEnvironment') IS NOT NULL
BEGIN
DROP TABLE testEnvironment;
END;
SELECT IDENTITY(INT,1,1) AS ID, firstName, secondName, eMail,
mailAddress + b.N + b.N AS mailAddress
INTO testEnvironment
FROM #testNames3 a
INNER JOIN #testNames b ON a.jID = b.ID;
To increase the number of rows, change this part of the code: -
IF object_id('tempdb..#testNames2') IS NOT NULL
BEGIN
DROP TABLE #testNames2;
END;
SELECT IDENTITY(INT,1,1) AS ID, firstName, secondName
INTO #testNames2
FROM (SELECT firstName, secondName
FROM (SELECT TOP 1000 --1000 * 1000 = 1,000,000 rows
N AS firstName
FROM #testNames
ORDER BY NEWID()) a
CROSS JOIN (SELECT TOP 1000 --1000 * 1000 = 1,000,000 rows
N AS secondName
FROM #testNames
ORDER BY NEWID()) b) innerQ;
The 1000 can be changed to a max of 152100 (the number of unique combinations).
March 14, 2012 at 12:55 pm
Why tempDB? I'm going to use a DB called my DB for that table. Would it still create the table in tempdb even if I used my own table name?
March 15, 2012 at 1:45 am
I think I need a real server to create those 100 million rows. But I got 1 million rows in 3 seconds or so 🙂
March 15, 2012 at 3:22 am
beginnew2012 (3/14/2012)
Why tempDB? I'm going to use a DB called my DB for that table. Would it still create the table in tempdb even if I used my own table name?
Prefixing a table with a hash (#) has a special meaning in T-SQL and makes it a local temporary table (they are only visible within the session that created the table and are dropped as soon as that session ends, regardless of whether they're explicitly dropped or not). These are always created in TempDB. If you want to create a local table, remove the hash and it can be created as a permanent table.
March 15, 2012 at 3:45 am
This is what I have done
Thank you
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply