Everything else aside two of the most important tasks of a database are to get data in, and get data back out again. We did out last month, so let’s do in this month. Yea, it’s backward. Not the first time I’ve done that, won’t be the last. Either way, insert statements are a bit obvious so I’m skipping those and concentrating on other ways to load data. Try out at least 4 of the following. You can use either a file (CSV or some other format) or another database. Try to mix it up some, and if you have the option try to hit a database other than SQL Server.
- The BULK INSERT command. This is a T-SQL command so can easily be added to other T-SQL code to perform some pretty impressive tasks.
- The OPENROWSET/OPENDATASOURCE/OPENQUERY commands. These are also T-SQL commands and can easily be added to other T-SQL code to perform some pretty impressive tasks. They also handy for linking data from other data sources into a T-SQL Query. Spend some time with these. Note: They are similar but different commands. Pay attention to the differences so you have a better idea when to use each.
- BCP – A command-line tool used to import and export data. Great for automation.
- Import/Export wizard – Creates an SSIS package that can import or export data. This can be used as a one-time import/export or the package can be saved for later use/editing.
- SSIS – Create your own SSIS package manually. This is more work than using the wizard but you have an amazing amount of control. This is also great for automation.
- Powershell – PoSH can do anything. Really. I’ve used it to wash my dishes on a couple of occasions. Create a script to get the data from a table and export it. Another great tool for automation.
- The coding language of your choice. – Same as Powershell really. If you have some development background, pick a language and write yourself a script to export a table.
- Other methods – Any other methods you can think of.