开发者

Removing unwanted SQL queries based on a condition

开发者 https://www.devze.com 2023-03-15 03:04 出处:网络
I have not had experience in SQL queries or SQL database , so please excuse me if my terminology is wrong.

I have not had experience in SQL queries or SQL database , so please excuse me if my terminology is wrong.

So, I have a file containing around 17,000 SQL insert statements where I enter data for 5 columns/attributes in a database. In those 17,000 statements there are only around 1200 statements which have data for all of the 5 columns/attributes while the rest have dat开发者_Go百科a only for 4 columns. I need to delete all those unwanted statements( which dont have data for all 5 columns).

Is there a simple way/process to do it other than going one by one and deleting? If so, it would be great if someone could help me out with it.


A different approach from my fine colleagues here would be to run the file into a staging/disposable database. Use the delete that @Rob called out in his response to pare the table down to the desired dataset. Then use an excellent, free tool like SSMS Tools Pack to reverse engineer those insert statements.


I can think of two approaches:

1: Using SQL: insert all the data and then run a query that removes any records where it does not have all of the necessary data. If the table is not currently empty, keep track of the ID where your current data "ends" so that your query can use that as a WHERE statement.

DELETE FROM myTable WHERE a IS NULL OR b IS NULL /* etc. */

2: Process the SQL file with a regular expression: Use a text editor or command line to match either "bad" records or "good" records. Most text editors have a find and replace that allows you to use regular expressions. And command line you can use grep or other tools to process. Or even a script that parses in your language of choice, for that matter.


Open file in notepad++, replace all "bad" lines using regular expressions.

0

精彩评论

暂无评论...
验证码 换一张
取 消