Recently, I was tasked with creating a report to track how much our loan officers were spending on customer credit pulls. This data did not previously exist in our SQL Server database, but we did have about 6 months of raw invoice data contained in CSV files. In order to create the report, I needed a way to import all of this data into our database.
Early in my career, I would have gone through a two step process. First, I’d manually create the table structure. This would be done either through the Graphic User Interface (GUI) or via a CREATE TABLE statement. Either way, this is a painstakingly long process that involves manually copying column names and selecting the appropriate data types. Once that was done, I would then upload the data using the GUI (which by the way is painfully slow).
This process is extremely inefficient and time consuming. But with a bit more experience, I figured out better ways to create tables and import data from CSV files. And that’s what we’ll cover here!
How to Create Tables in SQL Server using CSV Files
SQL Server Management Studio and Azure Data Studio have a flat file import functionality. This gives you the ability to create the table structure and import the data in one go. Now the flat file import wizards are basically the same in both applications. But I recently started using Azure Data Studio because of some additional features it has. That said, the video below will demonstrate how this is done in Data Studio. If you don’t have azure data studio installed you can download it here!
Conclusion
Coming back to my project, I utilized the flat file import wizard to create the initial table structure and import the first file’s data. But I had 5 more months of data that needed to be added to this new table. To finish inserting this information, I could have used another wizard in SQL Server Management Studio called “import data wizard”. Within this second wizard, I would be able to specify the target table and the data source. This is definitely a great option and I’ve considered creating another video to demonstrate how it works. But for my project, I opted for a Python script that would take the CSV data and automatically insert it into my new table. This saved me the trouble of having to walk through the import data wizard each time.
Now you know how to create tables from CSV files. Thank you for reading and I hope this new skill can come in handy on your next database project!
If you liked this article, we recommend you check out this other article on Versioning SQL Server Databases!