SQL Server Part5 - Advance Level

SQL Server Part5 - Advance Level free download

SQL Server Referential Integrity , importing Excel data and challenges you may face.

Topic1: Importing Data from Excel into SQL Server Using SQL: Key Points & Challenges

Use of OPENROWSET or OPENDATASOURCE: You can import Excel data using SQL queries like SELECT * FROM OPENROWSET(...), but it requires necessary drivers and access permissions.

Common Driver Requirement: For .xlsx files, the "Microsoft.ACE.OLEDB.12.0" provider is typically required. It must be installed separately if missing.

How to Install Drivers: Download and install Access Database Engine (ACE OLEDB) from Microsoft’s official site to avoid provider errors.

Typical Error - “Cannot initialize the data source object of OLE DB provider”: This often means the driver is missing, SQL Server is running as a service, or the Excel file path is incorrect.

Enable Ad Hoc Queries: sp_configure 'Ad Hoc Distributed Queries', 1 must be run (with RECONFIGURE) to allow ad hoc Excel file queries using OPENROWSET.

Preparing Excel for Import: Use Named Ranges (via Name Manager) or convert data to an Excel Table with a defined name like SalesData for cleaner and safer import syntax.

Accessing Table by Name: Use SQL like SELECT * FROM OPENROWSET(..., 'SELECT * FROM [SalesData$]') to read table-format Excel ranges.

Ensure Column Matching: SQL Server expects consistent column names and data types between Excel and the target SQL table. Clean Excel data before import.

Topic2: Referential Integrity & Constraint Management (Advanced SQL Concepts)

Foreign Key with ON DELETE CASCADE: When enabled, deleting a record from the parent table automatically deletes related records in the child table.

Foreign Key with ON UPDATE CASCADE: Updating a primary key in the parent table automatically updates the foreign key in the child table.

No CASCADE (Default): If ON DELETE/UPDATE CASCADE is not specified, you’ll get errors when trying to delete or update a parent record with child dependencies.

Disabling Constraints Temporarily: Use ALTER TABLE <table> NOCHECK CONSTRAINT <constraint_name> to temporarily disable FK constraints (useful during bulk imports).

Re-Enabling Constraints: Use ALTER TABLE <table> CHECK CONSTRAINT <constraint_name> to reenable constraints after your operation is complete.

Permanent Constraint Removal: Use ALTER TABLE <table> DROP CONSTRAINT <constraint_name> when the relationship is no longer required, but beware—it removes enforcement entirely.

Effect on Queries:

• Without constraints, accidental deletes or orphan inserts can occur.

• With constraints ON, child cannot exist without parent (INSERT fails if FK not matched), and parent cannot be deleted if children exist (unless CASCADE is enabled).