Today, we demonstrated building an SQL Server database from CSV files. We had multiple CSVs, each representing a table (‘brand’, ‘category’, ‘channel’, ‘customer’, ‘method’, ‘product’, ‘staff’, ‘store’), except ‘sales’, which was split into three files. This is common when migrating from simpler databases struggling with large datasets or multi-user access. We exported tables to CSV and then imported them into SQL Server using SQL Server Management Studio.
First, we created a new database named ‘tech store’, specifying locations for the MDF and log files. Instead of manual table creation, we used the ‘Import Flat File’ wizard, which automatically creates tables during import. We right-clicked the database, went to ‘Tasks’, and selected ‘Import Flat File’. We chose the first CSV (e.g., ‘brand’), named the table, and reviewed data types, setting ‘branch ID’ as the primary key. Clicking ‘Finish’ created the ‘brand’ table and imported data. We repeated this for other individual table CSVs.
For the ‘sales’ data (‘sales1.csv’), we again used ‘Import Flat File’, naming the table ‘sales’, reviewing data types (integer for IDs/quantity, money for discount, date/time appropriately), and setting ‘sales ID’ as primary key. Import time varied with data size.
To incorporate the remaining ‘sales’ files (‘sales2.csv’, ‘sales3.csv’), we could either use ‘Import Flat File’ to append data or import them into temporary tables (‘sales2’, ‘sales3’) and then use INSERT INTO sales SELECT * FROM sales2; and similarly for ‘sales3’, followed by deleting the temporary tables. This consolidated all sales data into a single ‘sales’ table.
Alternative methods include BULK INSERT and OPENROWSET (requiring pre-created tables), SSIS, and scripting languages. However, the ‘Import Flat File’ wizard is often the simplest and safest, especially for beginners, though data type and length mismatches are always potential issues during import. This walkthrough illustrated a straightforward method for creating an SQL Server database from CSV files.
Download CSV files: https://drive.google.com/file/d/1yRsw1ByroVi8E7fJD8ZUBJvhSlO3Ru1F/view?usp=sharing
#SQLServer #Database #CSVImport #DataMigration #TechStore #FlatFile #SSMS #DatabaseCreation #DataManagement