| Groups | Blog | Home |
sql server programming : Mass "Fake" Insert
definitely a good way to get large numbers of 'something' using set-based logic. -- TheSQLGuru President Indicium Resources, Inc. [quoted text, click to view] "Mike C#" <xyz@xyz.com> wrote in message news:etH5KdSjHHA.1900@TK2MSFTNGP04.phx.gbl... > You could do something like this. If you need more variety in your names, > insert more names into the Firstnames and Lastnames tables: > > CREATE TABLE Emp (EmpID int identity(1, 1) not null primary key, > LastName varchar(12), > FirstName varchar(12)) > > CREATE TABLE Firstnames (FirstName varchar(12) not null primary key) > > INSERT INTO Firstnames (FirstName) > VALUES ('Joe') > INSERT INTO Firstnames (FirstName) > VALUES ('Louis') > INSERT INTO Firstnames (FirstName) > VALUES ('Tommy') > INSERT INTO Firstnames (FirstName) > VALUES ('Mike') > INSERT INTO Firstnames (FirstName) > VALUES ('Michael') > INSERT INTO Firstnames (FirstName) > VALUES ('Benny') > INSERT INTO Firstnames (FirstName) > VALUES ('Tony') > INSERT INTO Firstnames (FirstName) > VALUES ('John') > INSERT INTO Firstnames (FirstName) > VALUES ('Harold') > INSERT INTO Firstnames (FirstName) > VALUES ('Han') > > CREATE TABLE Surnames (LastName varchar(12) not null primary key) > > INSERT INTO Surnames (LastName) > VALUES ('Jones') > INSERT INTO Surnames (LastName) > VALUES ('Johnson') > INSERT INTO Surnames (LastName) > VALUES ('Celko') > INSERT INTO Surnames (LastName) > VALUES ('Lewis') > INSERT INTO Surnames (LastName) > VALUES ('Henry') > INSERT INTO Surnames (LastName) > VALUES ('Williams') > INSERT INTO Surnames (LastName) > VALUES ('Williamson') > INSERT INTO Surnames (LastName) > VALUES ('General') > INSERT INTO Surnames (LastName) > VALUES ('Washington') > INSERT INTO Surnames (LastName) > VALUES ('Lincoln') > INSERT INTO Surnames (LastName) > VALUES ('Reagan') > INSERT INTO Surnames (LastName) > VALUES ('Clinton') > INSERT INTO Surnames (LastName) > VALUES ('Ford') > INSERT INTO Surnames (LastName) > VALUES ('Bush') > INSERT INTO Surnames (LastName) > VALUES ('Thompson') > INSERT INTO Surnames (LastName) > VALUES ('Thomas') > INSERT INTO Surnames (LastName) > VALUES ('Solo') > > INSERT INTO Emp (LastName, FirstName) > SELECT TOP 1000000 s.LastName, f.FirstName > FROM Surnames s > CROSS JOIN Firstnames f > CROSS JOIN INFORMATION_SCHEMA.COLUMNS c1 > CROSS JOIN INFORMATION_SCHEMA.COLUMNS c2 > CROSS JOIN INFORMATION_SCHEMA.COLUMNS c3 > > SELECT COUNT(*) > FROM Emp > > DROP TABLE Firstnames > DROP TABLE Surnames > DROP TABLE Emp > > > "sloan" <sloan@ipass.net> wrote in message > news:OMUIEWSjHHA.4112@TK2MSFTNGP04.phx.gbl... >> Lets say I have a table >> >> Emp >> EmpID int , LastName varchar(12) , FirstName varchar(12) >> >> >> If I need to mass populate this table with... say.... 1,000,000 rows, >> what's >> the quickest way to do this? >> >> I am developing a new database, and a top thing on the list is to >> populate >> with alot of (like production will be) data. >> >> I've been using while @counter stuff, and looping to put in those >> 1,000,000 >> rows. >> >> I've thought creating a table >> >> TableToSelectFrom >> UID int >> >> putting 1,000,000 rows in there, and then I can could do: >> >> Insert Into dbo.Emp ( EmpID , LastName , FirstName ) >> Select top 10000 UID , 'Smith', 'Jimmy' from dbo.TableToSelectFrom >> (for 10,000 emps of course) >> >> >> and then >> Insert Into SomeOtherTable ( ID , A, B, C) >> Select top 50000 UID , 'a' , 'b' , 'c' From dbo.TableToSelectFrom >> (for 50,000 entries in that table) >> >> Seems like there is a better idea. >> >> I have to do this for 15-20 tables, and various numbers. >> >> >> >> > >
Lets say I have a table
Emp EmpID int , LastName varchar(12) , FirstName varchar(12) If I need to mass populate this table with... say.... 1,000,000 rows, what's the quickest way to do this? I am developing a new database, and a top thing on the list is to populate with alot of (like production will be) data. I've been using while @counter stuff, and looping to put in those 1,000,000 rows. I've thought creating a table TableToSelectFrom UID int putting 1,000,000 rows in there, and then I can could do: Insert Into dbo.Emp ( EmpID , LastName , FirstName ) Select top 10000 UID , 'Smith', 'Jimmy' from dbo.TableToSelectFrom (for 10,000 emps of course) and then Insert Into SomeOtherTable ( ID , A, B, C) Select top 50000 UID , 'a' , 'b' , 'c' From dbo.TableToSelectFrom (for 50,000 entries in that table) Seems like there is a better idea. I have to do this for 15-20 tables, and various numbers.
You could do something like this. If you need more variety in your names,
insert more names into the Firstnames and Lastnames tables: CREATE TABLE Emp (EmpID int identity(1, 1) not null primary key, LastName varchar(12), FirstName varchar(12)) CREATE TABLE Firstnames (FirstName varchar(12) not null primary key) INSERT INTO Firstnames (FirstName) VALUES ('Joe') INSERT INTO Firstnames (FirstName) VALUES ('Louis') INSERT INTO Firstnames (FirstName) VALUES ('Tommy') INSERT INTO Firstnames (FirstName) VALUES ('Mike') INSERT INTO Firstnames (FirstName) VALUES ('Michael') INSERT INTO Firstnames (FirstName) VALUES ('Benny') INSERT INTO Firstnames (FirstName) VALUES ('Tony') INSERT INTO Firstnames (FirstName) VALUES ('John') INSERT INTO Firstnames (FirstName) VALUES ('Harold') INSERT INTO Firstnames (FirstName) VALUES ('Han') CREATE TABLE Surnames (LastName varchar(12) not null primary key) INSERT INTO Surnames (LastName) VALUES ('Jones') INSERT INTO Surnames (LastName) VALUES ('Johnson') INSERT INTO Surnames (LastName) VALUES ('Celko') INSERT INTO Surnames (LastName) VALUES ('Lewis') INSERT INTO Surnames (LastName) VALUES ('Henry') INSERT INTO Surnames (LastName) VALUES ('Williams') INSERT INTO Surnames (LastName) VALUES ('Williamson') INSERT INTO Surnames (LastName) VALUES ('General') INSERT INTO Surnames (LastName) VALUES ('Washington') INSERT INTO Surnames (LastName) VALUES ('Lincoln') INSERT INTO Surnames (LastName) VALUES ('Reagan') INSERT INTO Surnames (LastName) VALUES ('Clinton') INSERT INTO Surnames (LastName) VALUES ('Ford') INSERT INTO Surnames (LastName) VALUES ('Bush') INSERT INTO Surnames (LastName) VALUES ('Thompson') INSERT INTO Surnames (LastName) VALUES ('Thomas') INSERT INTO Surnames (LastName) VALUES ('Solo') INSERT INTO Emp (LastName, FirstName) SELECT TOP 1000000 s.LastName, f.FirstName FROM Surnames s CROSS JOIN Firstnames f CROSS JOIN INFORMATION_SCHEMA.COLUMNS c1 CROSS JOIN INFORMATION_SCHEMA.COLUMNS c2 CROSS JOIN INFORMATION_SCHEMA.COLUMNS c3 SELECT COUNT(*) FROM Emp DROP TABLE Firstnames DROP TABLE Surnames DROP TABLE Emp [quoted text, click to view] "sloan" <sloan@ipass.net> wrote in message news:OMUIEWSjHHA.4112@TK2MSFTNGP04.phx.gbl... > Lets say I have a table > > Emp > EmpID int , LastName varchar(12) , FirstName varchar(12) > > > If I need to mass populate this table with... say.... 1,000,000 rows, > what's > the quickest way to do this? > > I am developing a new database, and a top thing on the list is to > populate > with alot of (like production will be) data. > > I've been using while @counter stuff, and looping to put in those > 1,000,000 > rows. > > I've thought creating a table > > TableToSelectFrom > UID int > > putting 1,000,000 rows in there, and then I can could do: > > Insert Into dbo.Emp ( EmpID , LastName , FirstName ) > Select top 10000 UID , 'Smith', 'Jimmy' from dbo.TableToSelectFrom > (for 10,000 emps of course) > > > and then > Insert Into SomeOtherTable ( ID , A, B, C) > Select top 50000 UID , 'a' , 'b' , 'c' From dbo.TableToSelectFrom > (for 50,000 entries in that table) > > Seems like there is a better idea. > > I have to do this for 15-20 tables, and various numbers. > > > >
I used INFORMATION_SCHEMA because the OP didn't specify SQL 2000 or 2005.
INFORMATION_SCHEMA is available on both. [quoted text, click to view] "TheSQLGuru" <kgboles@earthlink.net> wrote in message news:%23T%23$uqSjHHA.5012@TK2MSFTNGP06.phx.gbl... > CROSS JOINing some tables (master.syscolumns is a good candidate) is > definitely a good way to get large numbers of 'something' using set-based > logic. > > -- > TheSQLGuru > President > Indicium Resources, Inc. > > "Mike C#" <xyz@xyz.com> wrote in message > news:etH5KdSjHHA.1900@TK2MSFTNGP04.phx.gbl... >> You could do something like this. If you need more variety in your >> names, insert more names into the Firstnames and Lastnames tables: >> >> CREATE TABLE Emp (EmpID int identity(1, 1) not null primary key, >> LastName varchar(12), >> FirstName varchar(12)) >> >> CREATE TABLE Firstnames (FirstName varchar(12) not null primary key) >> >> INSERT INTO Firstnames (FirstName) >> VALUES ('Joe') >> INSERT INTO Firstnames (FirstName) >> VALUES ('Louis') >> INSERT INTO Firstnames (FirstName) >> VALUES ('Tommy') >> INSERT INTO Firstnames (FirstName) >> VALUES ('Mike') >> INSERT INTO Firstnames (FirstName) >> VALUES ('Michael') >> INSERT INTO Firstnames (FirstName) >> VALUES ('Benny') >> INSERT INTO Firstnames (FirstName) >> VALUES ('Tony') >> INSERT INTO Firstnames (FirstName) >> VALUES ('John') >> INSERT INTO Firstnames (FirstName) >> VALUES ('Harold') >> INSERT INTO Firstnames (FirstName) >> VALUES ('Han') >> >> CREATE TABLE Surnames (LastName varchar(12) not null primary key) >> >> INSERT INTO Surnames (LastName) >> VALUES ('Jones') >> INSERT INTO Surnames (LastName) >> VALUES ('Johnson') >> INSERT INTO Surnames (LastName) >> VALUES ('Celko') >> INSERT INTO Surnames (LastName) >> VALUES ('Lewis') >> INSERT INTO Surnames (LastName) >> VALUES ('Henry') >> INSERT INTO Surnames (LastName) >> VALUES ('Williams') >> INSERT INTO Surnames (LastName) >> VALUES ('Williamson') >> INSERT INTO Surnames (LastName) >> VALUES ('General') >> INSERT INTO Surnames (LastName) >> VALUES ('Washington') >> INSERT INTO Surnames (LastName) >> VALUES ('Lincoln') >> INSERT INTO Surnames (LastName) >> VALUES ('Reagan') >> INSERT INTO Surnames (LastName) >> VALUES ('Clinton') >> INSERT INTO Surnames (LastName) >> VALUES ('Ford') >> INSERT INTO Surnames (LastName) >> VALUES ('Bush') >> INSERT INTO Surnames (LastName) >> VALUES ('Thompson') >> INSERT INTO Surnames (LastName) >> VALUES ('Thomas') >> INSERT INTO Surnames (LastName) >> VALUES ('Solo') >> >> INSERT INTO Emp (LastName, FirstName) >> SELECT TOP 1000000 s.LastName, f.FirstName >> FROM Surnames s >> CROSS JOIN Firstnames f >> CROSS JOIN INFORMATION_SCHEMA.COLUMNS c1 >> CROSS JOIN INFORMATION_SCHEMA.COLUMNS c2 >> CROSS JOIN INFORMATION_SCHEMA.COLUMNS c3 >> >> SELECT COUNT(*) >> FROM Emp >> >> DROP TABLE Firstnames >> DROP TABLE Surnames >> DROP TABLE Emp >> >> >> "sloan" <sloan@ipass.net> wrote in message >> news:OMUIEWSjHHA.4112@TK2MSFTNGP04.phx.gbl... >>> Lets say I have a table >>> >>> Emp >>> EmpID int , LastName varchar(12) , FirstName varchar(12) >>> >>> >>> If I need to mass populate this table with... say.... 1,000,000 rows, >>> what's >>> the quickest way to do this? >>> >>> I am developing a new database, and a top thing on the list is to >>> populate >>> with alot of (like production will be) data. >>> >>> I've been using while @counter stuff, and looping to put in those >>> 1,000,000 >>> rows. >>> >>> I've thought creating a table >>> >>> TableToSelectFrom >>> UID int >>> >>> putting 1,000,000 rows in there, and then I can could do: >>> >>> Insert Into dbo.Emp ( EmpID , LastName , FirstName ) >>> Select top 10000 UID , 'Smith', 'Jimmy' from dbo.TableToSelectFrom >>> (for 10,000 emps of course) >>> >>> >>> and then >>> Insert Into SomeOtherTable ( ID , A, B, C) >>> Select top 50000 UID , 'a' , 'b' , 'c' From dbo.TableToSelectFrom >>> (for 50,000 entries in that table) >>> >>> Seems like there is a better idea. >>> >>> I have to do this for 15-20 tables, and various numbers. >>> >>> >>> >>> >> >> > >
sloan
Also you can try to remove all indexes on target table to speed up INSERT. Test it. [quoted text, click to view] "sloan" <sloan@ipass.net> wrote in message news:OMUIEWSjHHA.4112@TK2MSFTNGP04.phx.gbl... > Lets say I have a table > > Emp > EmpID int , LastName varchar(12) , FirstName varchar(12) > > > If I need to mass populate this table with... say.... 1,000,000 rows, > what's > the quickest way to do this? > > I am developing a new database, and a top thing on the list is to > populate > with alot of (like production will be) data. > > I've been using while @counter stuff, and looping to put in those > 1,000,000 > rows. > > I've thought creating a table > > TableToSelectFrom > UID int > > putting 1,000,000 rows in there, and then I can could do: > > Insert Into dbo.Emp ( EmpID , LastName , FirstName ) > Select top 10000 UID , 'Smith', 'Jimmy' from dbo.TableToSelectFrom > (for 10,000 emps of course) > > > and then > Insert Into SomeOtherTable ( ID , A, B, C) > Select top 50000 UID , 'a' , 'b' , 'c' From dbo.TableToSelectFrom > (for 50,000 entries in that table) > > Seems like there is a better idea. > > I have to do this for 15-20 tables, and various numbers. > > > >
Thanks everyone. But I think we're all in the same ball park, you gotta have some table to pick from. If I go with my idea, [quoted text, click to view] > TableToSelectFrom1 > UID int > TableToSelectFrom2 > UID int I can at least CROSS JOIN those two, and hten I don't need 1,000,000 rows , I can get by with 1,000 in each table. So that will help. I'll check out the two "donor" tables you mention. [quoted text, click to view] "sloan" <sloan@ipass.net> wrote in message news:OMUIEWSjHHA.4112@TK2MSFTNGP04.phx.gbl... > Lets say I have a table > > Emp > EmpID int , LastName varchar(12) , FirstName varchar(12) > > > If I need to mass populate this table with... say.... 1,000,000 rows, what's > the quickest way to do this? > > I am developing a new database, and a top thing on the list is to populate > with alot of (like production will be) data. > > I've been using while @counter stuff, and looping to put in those 1,000,000 > rows. > > I've thought creating a table > > TableToSelectFrom > UID int > > putting 1,000,000 rows in there, and then I can could do: > > Insert Into dbo.Emp ( EmpID , LastName , FirstName ) > Select top 10000 UID , 'Smith', 'Jimmy' from dbo.TableToSelectFrom > (for 10,000 emps of course) > > > and then > Insert Into SomeOtherTable ( ID , A, B, C) > Select top 50000 UID , 'a' , 'b' , 'c' From dbo.TableToSelectFrom > (for 50,000 entries in that table) > > Seems like there is a better idea. > > I have to do this for 15-20 tables, and various numbers. > > > >
There are several open source test database generators; google it.
Much more appropriate! I just hate the darn long names though. :)
-- TheSQLGuru President Indicium Resources, Inc. [quoted text, click to view] "Mike C#" <xyz@xyz.com> wrote in message news:uT%23PONTjHHA.1900@TK2MSFTNGP04.phx.gbl... >I used INFORMATION_SCHEMA because the OP didn't specify SQL 2000 or 2005. >INFORMATION_SCHEMA is available on both. > > "TheSQLGuru" <kgboles@earthlink.net> wrote in message > news:%23T%23$uqSjHHA.5012@TK2MSFTNGP06.phx.gbl... >> CROSS JOINing some tables (master.syscolumns is a good candidate) is >> definitely a good way to get large numbers of 'something' using set-based >> logic. >> >> -- >> TheSQLGuru >> President >> Indicium Resources, Inc. >> >> "Mike C#" <xyz@xyz.com> wrote in message >> news:etH5KdSjHHA.1900@TK2MSFTNGP04.phx.gbl... >>> You could do something like this. If you need more variety in your >>> names, insert more names into the Firstnames and Lastnames tables: >>> >>> CREATE TABLE Emp (EmpID int identity(1, 1) not null primary key, >>> LastName varchar(12), >>> FirstName varchar(12)) >>> >>> CREATE TABLE Firstnames (FirstName varchar(12) not null primary key) >>> >>> INSERT INTO Firstnames (FirstName) >>> VALUES ('Joe') >>> INSERT INTO Firstnames (FirstName) >>> VALUES ('Louis') >>> INSERT INTO Firstnames (FirstName) >>> VALUES ('Tommy') >>> INSERT INTO Firstnames (FirstName) >>> VALUES ('Mike') >>> INSERT INTO Firstnames (FirstName) >>> VALUES ('Michael') >>> INSERT INTO Firstnames (FirstName) >>> VALUES ('Benny') >>> INSERT INTO Firstnames (FirstName) >>> VALUES ('Tony') >>> INSERT INTO Firstnames (FirstName) >>> VALUES ('John') >>> INSERT INTO Firstnames (FirstName) >>> VALUES ('Harold') >>> INSERT INTO Firstnames (FirstName) >>> VALUES ('Han') >>> >>> CREATE TABLE Surnames (LastName varchar(12) not null primary key) >>> >>> INSERT INTO Surnames (LastName) >>> VALUES ('Jones') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Johnson') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Celko') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Lewis') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Henry') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Williams') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Williamson') >>> INSERT INTO Surnames (LastName) >>> VALUES ('General') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Washington') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Lincoln') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Reagan') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Clinton') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Ford') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Bush') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Thompson') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Thomas') >>> INSERT INTO Surnames (LastName) >>> VALUES ('Solo') >>> >>> INSERT INTO Emp (LastName, FirstName) >>> SELECT TOP 1000000 s.LastName, f.FirstName >>> FROM Surnames s >>> CROSS JOIN Firstnames f >>> CROSS JOIN INFORMATION_SCHEMA.COLUMNS c1 >>> CROSS JOIN INFORMATION_SCHEMA.COLUMNS c2 >>> CROSS JOIN INFORMATION_SCHEMA.COLUMNS c3 >>> >>> SELECT COUNT(*) >>> FROM Emp >>> >>> DROP TABLE Firstnames >>> DROP TABLE Surnames >>> DROP TABLE Emp >>> >>> >>> "sloan" <sloan@ipass.net> wrote in message >>> news:OMUIEWSjHHA.4112@TK2MSFTNGP04.phx.gbl... >>>> Lets say I have a table >>>> >>>> Emp >>>> EmpID int , LastName varchar(12) , FirstName varchar(12) >>>> >>>> >>>> If I need to mass populate this table with... say.... 1,000,000 rows, >>>> what's >>>> the quickest way to do this? >>>> >>>> I am developing a new database, and a top thing on the list is to >>>> populate >>>> with alot of (like production will be) data. >>>> >>>> I've been using while @counter stuff, and looping to put in those >>>> 1,000,000 >>>> rows. >>>> >>>> I've thought creating a table >>>> >>>> TableToSelectFrom >>>> UID int >>>> >>>> putting 1,000,000 rows in there, and then I can could do: >>>> >>>> Insert Into dbo.Emp ( EmpID , LastName , FirstName ) >>>> Select top 10000 UID , 'Smith', 'Jimmy' from dbo.TableToSelectFrom >>>> (for 10,000 emps of course) >>>> >>>> >>>> and then >>>> Insert Into SomeOtherTable ( ID , A, B, C) >>>> Select top 50000 UID , 'a' , 'b' , 'c' From dbo.TableToSelectFrom >>>> (for 50,000 entries in that table) >>>> >>>> Seems like there is a better idea. >>>> >>>> I have to do this for 15-20 tables, and various numbers. >>>> >>>> >>>> >>>> >>> >>> >> >> > >
[quoted text, click to view]
On May 3, 10:20 am, "sloan" <s...@ipass.net> wrote: > Thanks everyone. > > But I think we're all in the same ball park, you gotta have some table to > pick from. > On May 3, 10:20 am, "sloan" <s...@ipass.net> wrote: > Thanks everyone. > > But I think we're all in the same ball park, you gotta have some table to > pick from. > Au contraire, you do not need a table to pick from. CREATE TABLE #LotsaNames ( N int IDENTITY(1,1), Firstname varchar(30), Lastname varchar(30) ) INSERT LotsaNames (Firstname, Lastname) SELECT TOP 10000 Firstnames.Firstname, Lastnames.Lastname FROM ( SELECT 'Louis' as Firstname UNION ALL SELECT 'Tommy' UNION ALL SELECT 'Mike' UNION ALL SELECT 'Michael' UNION ALL SELECT 'Benny' UNION ALL SELECT 'Tony' UNION ALL SELECT 'John' UNION ALL SELECT 'Harold' UNION ALL SELECT 'Han' ) Firstnames, ( SELECT 'Jones' as Lastname UNION ALL SELECT 'Johnson' UNION ALL SELECT 'Celko' UNION ALL SELECT 'Lewis' UNION ALL SELECT 'Henry' UNION ALL SELECT 'Williams' UNION ALL SELECT 'Williamson' UNION ALL SELECT 'General' UNION ALL SELECT 'Washington' UNION ALL SELECT 'Lincoln' UNION ALL SELECT 'Reagan' UNION ALL SELECT 'Clinton' UNION ALL SELECT 'Ford' UNION ALL SELECT 'Bush' UNION ALL SELECT 'Thompson' UNION ALL SELECT 'Thomas' UNION ALL SELECT 'Solo' ) Lastnames, ( SELECT 0 as n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) N1, ( SELECT 0 as n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) N2, ( SELECT 0 as n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) N3, ( SELECT 0 as n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) N4, ( SELECT 0 as n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) N5 If only numbers were being generated, then you wouldn't even need the temp table with the identity -- you could construct a number from 1 to 10000 thus: N1.n + 10*N2.n + 100*N3.n + 1000*N4.n + 10000*N5.n + 1 But adding the extra name columns means that each combination of digits is going to be repeated a lot, so you won't get unique numbers. Perhaps some trickery with DISTINCT or GROUP BY could work around this, but I can't place it right now. I got this technique off this very newsgroup a few years ago; in fact, one of the first things I add to a new database is a DIGITS view and a NATURALNUMBERS view based on it.
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |