CODEWITHSUNDEEP
sqlsql-serverdata-cleaning
Marco
Marco

Reputation: 9

Data Cleaning in SQL 17

I am new to SQL and I am currently struggling with the cleaning of a large Database.

In the Link below you see the Data I am working with. This basically are the financial accounts of all Insurance companies listed in Austria for the year 2018.

What I now wanted to do is to delete the Columns F1 and F2 as well as delete the Rows 1-4 from each table, to rename the column names and to unpivot the data. (this is my understanding of cleaning :)…) Since the database contains many tables, I was wondering, do you know a command which helps me f.e. to delete the Columns F1 and F2 in all tables at once? I do not want to have to change every table manually since I do have date until the year 2014.

Data I am working with

Upvotes: 0

Views: 359

Answers (2)

Marco
Marco

Reputation: 9

Thank you all very much for your fast Responses. I will have to elaborate on those since I am a complete beginner and do not really understand your comments now :) .. but I am working on it.

Regarding the first 4 rows, I basically want to delete every empty (null) cell. By deleting the first 4 rows, I have to admit I would also delete a Header, but I could live with that "mistake" in order to simplify the process

Upvotes: 0

Srinika Pinnaduwage
Srinika Pinnaduwage

Reputation: 1042

I'll give you some heads up, but without knowing exact details its hard to give a complete solution.

To get the Table and Column Names You can use a query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME like N'2018%'

or you may change the TABLE_NAME like as needed.

If you want to see the query and execute it, you can extend the above to give you the Alter Table as follows;

SELECT distinct 'Alter table ' + Table_Schema + '.' + Table_Name + ' drop column F1'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME like N'2018%'

This will generate you the scripts as necessary. You can extend these scripts, by changing the Select string and the where clause, if necessary with regex to catch all the tables in one shot.

As @HoneyBadger pointed out, you should know what is first 4 rows as, what you see in the management studio as first 4 rows may not be those inside the database itself. What you can do is query what you believe as those and find a way to identify them specifically and then write delete statements as the above dynamic statements and execute.

Anyway, keep a backup ready, just in case

Upvotes: 1

Related Questions