Best practices for Data Importing using SQL Server native methods
Ever wondered SQL Server Data Importing Do's and Don'ts, with a set of best practices. Import of data is a common task in any database platform and when it becomes regular you have to take care lot of incompatibilities.
In particular SQL Server DBAs often find themselves importing and massaging data using T-SQL. Why? Because some transformations of the data simply require the power of full-fledged SQL. Having just recently survived yet another case of data importing, I was inspired to assemble a list of do's and don'ts that work for me. Raw data - what you call from legacy systems with text format and when source & destination columns are matched, you have no problem. But for further calculations you have to perform using a staging table without affecting the live tables on the database. Never attempt to load the data directly on the live tables and perform such calculations.
You are aware about DTS & SSIS to load raw text data into corresponding raw tables, common data type used is VARCHAR data type, easy step with DTS/SSIS. By default DTS will go with name the columns as COL001 and so forth if you don't supply column names ahead of time. Say if you have data that is inconsistent as compared to the values within the table relationships, so you need to have good checks and data is not striped/truncated.
Whereever possible do not use non-alphanumeric characters within the staging tables, this will reduce the control over the import with raw data. Good to use or force the delimit the columns with square brackets or double quotes. Do not use KEYWORDS in columns names, very common mistake within the legacy systems having descriptive column names that can wreak havoc with your SQL queries. So again you'll end up having to delimit the keyword column names with square brackets or double quotes.
Few times above I have mentioned to use staging tables and to add-up using with correct/matching data types between source and destination data sources. I would always go with staging tables with the same column names as the destination tables in the target OLTP database. This will reduce to save your time when the import fails and what's more important (for me) is that the data types of each column in the raw data will be checked and corrected as it's loaded into the staging. It is possibile to add new columns to the staging tables, as the source table may be changed over the period of time. This has the advantage that the original data exists alongside the newly split data, and you can test your scripts just by comparing the columns.
Next steps: When you're ready to insert data from your staging tables, test it first by inserting into local copies of the production tables. Sometimes you only need empty tables; other times, they'll have to be populated. Always keep the production constraints on the copied tables. That way, you can test how well your staging data will satisfy those constraints. These constraints consist of NULL, default, check, primary key, and foreign key constraints. Don't miss for simple table columns defaults such as NULL or NOT NULL properties on their columns as the target system, and then work up through all the remaining constraints. If your tests show that the staging data insert process satisfies all those constraints, you're that much closer to success.
Always test the import first and then schedule it to perform on Production server, do not attempt to execute when there are busy times on the live server.