When we think of importing and exporting data into SQL Server, whether it be from Excel spreadsheets or some other source, we do have to be careful.
If we want to import data into a Production system – this is probably a bad idea period. If proper monitoring is happening on the server, someone will probably be over to your desk soon (or a short concise email will be sent to you) asking that you kindly not do this again. Do not take this as a sign that you can’t do your job. Take it as a sign of someone else doing their job correctly – making sure that nothing comes into the system that shouldn’t be there.
If we want to import data into a QA or UAT system, this can be okay – depending upon how your environments are set up. Some teams don’t want anything to potentially break what they are testing. When in doubt, discuss it with the team.
If we want to import data into a Development system, this is probably going to be okay. You should still ask the team if it’s okay to import the data – especially if it’s a table that doesn’t exist as of yet. They may be wanting data to be imported through other means so check with the team first before attempting anything we’ve discussed here.
If we want to export data from any system, there are several things to be aware of.
- Do not attempt to export data from a very busy table. You could cause what is known as deadlocking and completely stop processes from functioning.
- Do not export hundreds of thousands of rows of data from a table. This could cause an unexpected burden on the system and slow down mandatory processes.
- Do let someone know if you’ve attempted to get data from a table and the data you expect to be there just isn’t there.
- Do let someone know if you’re trying to get the data from a system multiple times because your original queries just weren’t quite right. The team is there to help you get it right.
- Do not think that getting all of the data from the system will give you the system. There are lots of parts and pieces that may be outside of the database to get the system to perform as it should.
- Do not set up the process to export (or import) data from your system and walk away. At any point, the system (not just the SQL Server but your own system that you’re working off of) could come to a halt and you’ve just run off to get one more packet of snacks!
So, next time you want to either import or export data from SQL Server, ask. The team may have other options that will make things easier for you and them. The team may appreciate you trying to take something off their plate, but it could also lead to other issues.
(Why a Prince song? Because it’s freakin’ Prince! And you should always question things…)
And with that, we actually wrap up our wrap up with Importing and Exporting data using SQL Server and Excel Spreadsheets. Until next time when we’ll discuss a brand new topic!