I have CSV files that I would like to import into SQL Server 2014. Each record in the files has around 20 columns, each of them should be validated for data type compliance as well as some other checks per column (min/max ranges, null/not null etc.). I'm looking for advice regarding a model architecture on doing this in a efficient way. Using SSIS and some error tables seems to me a bit inefficient since it looks as if I only can get one column checked at a time and the error handling (especially the column lockup in SSIS is clumsy at best). I would prefer to have each record processed and right away have an overview of all errors that are in that record, so that I can provide a feedback of all issues at once to the people providing the data for import. SSIS is, indeed, clunky when it comes to importing data for all the processing prowess that it seems to tout. One of the commenters, Kin I believe, had it right with creating a temporary table. Here's the code we use to import data directly from Excel into a temporary table to be parsed for compliance. As you can see in the example below, using T-SQL, OPENROWSET reads the specific tab on the Excel Spreadsheet called SQL Server Instance Details and imports the data into the SQL Table TempServerMap. After, the data is imported into the temporary table you have a world of options. You can insert the data into another table that has all the SQL business rules you need on each of the columns--kicking out the data that doesn't qualify--I say this not as a necessarily 'best way' but for using the least amount of code in the beginning fine tuning your process of finding errors. You'd need to control the error handling here to catch the bad apples (rows) so you can review them later. Alternately, you could create functions, or procedures, or triggers or all of the above that check the data in advance to make sure the do comply with business requirements. And if you're looking to communicate those errors to others, just create an SSRS (Reporting Services) report that queries your new error table and give them the proper permissions to view the report. It's web based and easily accessible in an intranet environment. If you have external associates you can have SSRS e-mail the report to them just as easily. The main point here is: get your data away from Excel and into SQL Server so it can be manipulated effectively. Some DBAs might be offended at the replace/exec of the code below. If that's the case, just hardcode the select statement with specific excel file names. Declare @cmdstring varchar(8000) ='select * into SQLSunSet.dbo.TempServerMap from OPENROWSET ('Microsoft.Ace.OLEDB.12.0', 'Excel 12.0;Database=', 'select * from [SQL Server Instance Details$]')' set @cmdstring = replace(@cmdstring,',@MapsExcelFile_SQLServerUsageTracker) if exists (select * from sysobjects where type ='u' and name ='TempServerMap') drop table TempServerMap --print (@cmdstring) exec (@cmdstring) Many people seem to turn to SSIS in hopes of not coding T-SQL. But I usually find that SSIS is like faulty four-wheel drive. It just get's you stuck more than you would have been a lot farther down the road. To use OPENROWSET effectively you might need to install the Microsoft Access Database Engine 2010 Redistributable (Really--a Microsoft Office Engine--not just for MS Access). SQL DB Validator performs database and data cube verification and validation testing. Tests the content of a database extract against its original data store using the specific SQL syntax for each of the data sources. The program works with MS SQL, mySQL, DB2, Oracle, Teradata, MS. SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct. SQL Create DB SQL Drop DB SQL Create Table SQL Drop Table SQL Alter Table SQL Constraints SQL Not. Validation in the database layer is preferred because you can have different applications connecting to the same database and while this is a good thing, the validation in those applications is not necessarily the same, therefore validation that is implemented on the database layer is better in terms of data. I have an issue related to data in sql server. In my database some of the constraint were not enabled i.e. They were not checked. How to validate data in sql server? Improved schema validation capabilities • Enhancements to XQuery support • Enhanced functionality for performing XML data manipulation language (DML) insertions The rest of this whitepaper examines these enhancements and demonstrates how you can use them to implement better XML solutions in SQL Server 2008. Anonymous 10-Aug-01 10:09 Anonymous 10-Aug-01 10:09 1 It will not work for all ODBC drivers. From the ODBC documentation (SQLPrepare) and past experience: 'Some drivers cannot return syntax errors or access violations when the application calls SQLPrepare. A driver can handle syntax errors and access violations, only syntax errors, or neither syntax errors nor access violations. Therefore, an application must be able to handle these conditions when calling subsequent related functions such as SQLNumResultCols, SQLDescribeCol, SQLColAttribute, and SQLExecute.' I have an issue related to data in sql server. In my database some of the constraint were not enabled i.e. They were not checked, After some time working on it we found this issue that a parent rows can be deleted without deleting child, which was an issue. I enabled all the constraint in the database using query ALTER TABLE tbl_name CHECK CONSTRAINT ALL above query was executed on all the tables of that database without any error. But my concern is whether it will work or not, if it will work on the existing data then what will happen to that data whose parent table data has been deleted. I want to know is there any way such that I can validate such data data whose parent record doesn't exist in the entire database. There are about 270 constraint containing FOREIGN KEY AND UNIQUE KEY. I don't want to go for manual option. Please help me out. ALTER TABLE tbl_name CHECK CONSTRAINT ALL only re-enables the constraints. Importantly, the constraints are not checked against the existing data in the database (nor are they trusted by the optimizer). If you want that to occur, you need to specify WITH CHECK as well: ALTER TABLE tbl_name WITH CHECK CHECK CONSTRAINT ALL (And yes, the word CHECK appears twice) If you execute this, and there are orphaned child rows (or other invalid constraints), then the ALTER TABLE will fail with an error message. There's nothing SQL Server can do to fix this issue - it's for you to decide whether to a) remove the orphaned rows, or b) to re-create, in some manner, a suitable parent row for them.
0 Comentarios
Deja una respuesta. |
AutorEscribe algo sobre ti mismo. No hay que ser elegante, sólo haz un resumen. Archivos
Mayo 2019
Categorías |