How to create sample data in SQL Server on Ubuntu is shown in this blog. These steps are available for Windows OS, too.
▼1. What is Microsoft SQL Server ?
There are many kinds of SQL Server. for example, Azure SQL Database in cloud, Azure Synapse Analytics – SQL pool (SQL Data Warehouse) and on-premise SQL Server on Azure Virtual Machine (VM) etc
We can find the version of SQL Server and hot fixes in this site Microsoft SQL Server Versions List . as Free trial version, SQL Server Express edition on Windows and Linux are available.
On windows OS, we can download Express edition by clicking “Download Now”. SQL2022-SSEI-Expr.exe can be downloaded and then the install wizard get started by clicking this exe file. https://www.microsoft.com/en-us/sql-server/sql-server-downloads
▼2. Prerequisites
The way to install SQL Server on Ubuntu is shown in this public document.
Quickstart: Install SQL Server and create a database on Ubuntu
▼3. Creating sample data in bulk
When doing some performance tests, sample data is required. in this case, this blog can be helpful. increasing the number of column and records in the following python code cause larger data to meet your request.
3-1. Preparation
“createdbtable.sql” is needed to create a sample database and a table.
// createdbtable.sql
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'sampleDB')
Begin
Create database sampleDB
End;
go
use sampleDB;
go
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'sampletable')
Begin
drop table sampletable
END;
go
Create table sampletable(
id int identity(1,1) not null,
num int,
cdatetime datetime,
note nvarchar(max)
)
goThis Transact-SQL query can create the database “sampleDB” and the table “sampletable”. the schema of this table is below.
Column_name Type ----------- -------- id int num int cdatetime datetime note nvarchar
3-2. Developing “insertmultipledata.sql” to create sample data by python
Ref: Python – Using Visual Studio Code on Ubuntu No.34
Let’s show a detail of the table “sampletable“. Automatically generated number is added into the column ”id”. a sequential number is added in the column “num”. the column “cdatetime” has the current datetime. the columne “note” contains the characters that are created by random method.
After executing this python code, the file “insertmultipledata.sql” is generated to insert sample data into the table “sampletable“.
# create random string
import random, string,datetime
# create variable dtnow get date time
dtnow = datetime.datetime.now()
# create function random_key for random string
def randomword(length):
letters = string.ascii_lowercase
return ''.join(random.choice(letters) for i in range(length))
# put insert query to the file insertmultipledata.sql
with open("insertmultipledata.sql", "w") as f:
f.write("INSERT INTO sampletable(num,cdatetime,note) VALUES (0,GETDATE(),'init')")
for num in range(1, 10): # 10 rows
f.write(",(%d,CAST('%s' as DATETIME2),'%s')" % (num,dtnow,randomword(num)))
f.write(";")This file insertmultipledata.sql that is created by above python code contains below.
INSERT INTO sampletable(num,cdatetime,note) VALUES (0,GETDATE(),'init'),(1,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'k'),(2,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'ef'),(3,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'rqo'),(4,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'gryz'),(5,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'sgvzk'),(6,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'tpngss'),(7,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'ylrhfmt'),(8,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'dkxoghpt'),(9,CAST('2022-10-20 14:00:30.053558' as DATETIME2),'whevhstvg');
3-3. Executing the sql statement in “insertmultipledata.sql” by sqlcmd.exe
Ref: sqlcmd Utility
// the value of "-P" is a password of "sa" user
> sqlcmd -S localhost -U sa -P yourpassowrd -i ./createdbtable.sql
(Output)
Changed database context to 'sampleDB'.
> sqlcmd -S localhost -U sa -P yourpassword -d sampleDB -i ./insertmultipledata.sql
(Output)
(10 rows affected)3-4. Confirming the generated sample data using sqlcmd.exe
sqlcmd -S localhost -U sa -P yourpassword -d sampleDB -Q"select * from sampletable;"
(output）
id num cdatetime note
----------- ----------- ----------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 0 2022-10-20 16:13:13.477 init
2 1 2022-10-20 16:12:11.777 a
3 2 2022-10-20 16:12:11.777 ah
4 3 2022-10-20 16:12:11.777 lam
5 4 2022-10-20 16:12:11.777 nddv
6 5 2022-10-20 16:12:11.777 vdhpw
7 6 2022-10-20 16:12:11.777 fdfszy
8 7 2022-10-20 16:12:11.777 rgofcgt
9 8 2022-10-20 16:12:11.777 hezwdqae
10 9 2022-10-20 16:12:11.777 bywqcunva
(10 rows affected)▼4. Reference
- Microsoft SQL Server Versions List
- Quickstart: Install SQL Server and create a database on Ubuntu
- Python – Using Visual Studio Code on Ubuntu No.34
- sqlcmd Utility
That’s all. Have a nice day ahead !!!