Create a dummy table from number table-SCRIPT

Neerajprasadsharma
Posted by Neerajprasadsharma under Sql Server category on | Points: 40 | Views : 619
This SQL server script uses the number table to create a test or dummy table for some tests.
Column names in the table doesn`t matter, you can change as per your requirement.



------------------------------------CREATE NUMBER TABLE
Create table Numbers (N Int Primary Key Clustered)

insert into Numbers
SELECT
TOP 500000 Convert ( Varchar (50) , Row_Number () Over (Order By S1.number ) )

FROM master.dbo.spt_values s1
CROSS JOIN master.dbo.spt_values s2
go
------------------------------------CREATE NUMBER TABLE

------------------------------------CREATE DUMMY TABLE
CREATE TABLE [DBO].[DummyTable] (
Primarykey int NOT NULL ,
Keycol VARCHAR(50) NOT NULL
,SearchCol INT
,SomeData char(20),

Keycol2 VARCHAR(50) NOT NULL
,SearchCol2 INT
,SomeData2 char(20),


Keycol3 VARCHAR(50) NOT NULL
,SearchCol3 INT
,SomeData3 char(20) )



------------------------------------CREATE DUMMY TABLE


------------------------------------INSERT DATA IN DUMMY TABLE

INSERT INTO [DBO].[DummyTable]
SELECT
n ,

n,
n
,'Some text..'
,
n,
n
,'Some text..'

,

n,
n
,'Some text..'

FROM Numbers

------------------------------------INSERT DATA IN DUMMY TABLE
------------------------------------ADD PRIMARY KEY IN DUMMY TABLE

ALTER TABLE DBO.[DummyTable]
ADD CONSTRAINT PK_Dummy PRIMARY KEY (Primarykey)
------------------------------------ADD PRIMARY KEY IN DUMMY TABLE


Comments or Responses

Login to post response