Sql Server: How to quickly create a sample table with dummy data
- Posted by Sqltimes
- On March 1, 2014
- 0 Comments
Quick post today:
Many times in the life of a DBA, we find ourselves in need to quickly create some sample table with dummy data to test some theory. Creating this from scratch every time takes some time (not much, but still some). This is a constant and repeating need and over years this adds up to a lot. So, I’ve created a sample table with immediately loadable dummy data.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
---- Start of Script : Sample_Table_With_Data.sql------ Sample Table Definition--USE DBADBGODROP TABLE dbo.SampleTableGOCREATE TABLE dbo.SampleTable( ID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_SampleTable_ID PRIMARY KEY , GUID_Col1 UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_GUID_Col1 DEFAULT NEWID() , GUID_Col2 UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_GUID_Col2 DEFAULT NEWID() , GUID_Col3 UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_GUID_Col3 DEFAULT NEWID() , SeqGUID_Col1 UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_SqeGUID_Col1 DEFAULT NEWSEQUENTIALID() , SeqGUID_Col2 UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_SqeGUID_Col2 DEFAULT NEWSEQUENTIALID() , SeqGUID_Col3 UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_SqeGUID_Col3 DEFAULT NEWSEQUENTIALID() , IntCol1 INT NOT NULL CONSTRAINT DF_IntCol1 DEFAULT CEILING(RAND() * 10000) , IntCol2 INT NOT NULL CONSTRAINT DF_IntCol2 DEFAULT CEILING(RAND() * 10000) , IntCol3 INT NOT NULL CONSTRAINT DF_IntCol3 DEFAULT CEILING(RAND() * 10000) , CharCol1 VARCHAR(50) NOT NULL CONSTRAINT DF_CharCol1 DEFAULT REPLICATE( CHAR((CEILING(RAND()*10) + 65)),CEILING((RAND()*10))) , CharCol2 VARCHAR(50) NOT NULL CONSTRAINT DF_CharCol2 DEFAULT REPLICATE( CHAR((CEILING(RAND()*10) + 65)),CEILING((RAND()*10))) , CharCol3 VARCHAR(50) NOT NULL CONSTRAINT DF_CharCol3 DEFAULT REPLICATE( CHAR((CEILING(RAND()*10) + 65)),CEILING((RAND()*10))) , DateCol1 DATETIME NOT NULL CONSTRAINT DF_DateCol1 DEFAULT DATEADD(DAY, -1 * CEILING(RAND()*1000) , GETDATE()) , DateCol2 DATETIME NOT NULL CONSTRAINT DF_DateCol2 DEFAULT DATEADD(DAY, -1 * CEILING(RAND()*1000) , GETDATE()) , Created_On DATETIME NOT NULL CONSTRAINT DF_Created_On DEFAULT GETDATE() , Created_By VARCHAR(50) NOT NULL CONSTRAINT DF_Created_By DEFAULT SYSTEM_USER)GO |
As you can see this has good variety of columns;
- Integers
- Fixed length characters
- Variable length characters
- Unique identifiers
- Datetime
All of them are populated with some random data. DEFAULT column constraints are used to insert random data, even for DATETIME column.
There are cases when I need it to have some indexes and statistics on the table. Below scripts add some of these:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
CREATE INDEX IX_IntCol123_SampleTable ON dbo.SampleTable(IntCol1, IntCol2, IntCol3)CREATE INDEX IX_IntCol3_SampleTable ON dbo.SampleTable(IntCol3)CREATE INDEX IX_IntCol2_SampleTable ON dbo.SampleTable(IntCol2)CREATE INDEX IX_SqeGUID_Col1_SampleTable ON dbo.SampleTable(SeqGUID_Col1)CREATE INDEX IX_SqeGUID_Col2_SampleTable ON dbo.SampleTable(SeqGUID_Col2)CREATE INDEX IX_SqeGUID_Col3_SampleTable ON dbo.SampleTable(SeqGUID_Col3)GOCREATE STATISTICS USX_GUID_Col1_SampleTable ON dbo.SampleTable(GUID_Col1)CREATE STATISTICS USX_GUID_Col2_SampleTable ON dbo.SampleTable(GUID_Col2)CREATE STATISTICS USX_GUID_Col3_SampleTable ON dbo.SampleTable(GUID_Col3)CREATE STATISTICS USX_CharCol3_SampleTable ON dbo.SampleTable(CharCol3)GO |
Now it is time to load the data. Repeating the same INSERT statement multiple times allows us to load as many records as needed.
|
1
2
|
INSERT INTO dbo.SampleTable DEFAULT VALUESGO 2000 |
SampleTable with data
Hope this helps,
_Sqltimes
0 Comments