DYnamic SQL Examples

Question

Recently, I learned what dynamic sql is, and one of the most interesting features for me is that we can use dynamic column and table names. But I can’t think of useful examples of real life. The only thing that occurred to me was a statistical table.

Say we have a table with name, type and created_data. Then we want to have a table that in the columns is the years from the created_data column, as well as the row type and the number of names created in years. (sorry for my English)

What could be other useful real-world examples of using dynamic sql with a column and table as parameters? How do you use it?

Thanks for any suggestions and help :) Regards Gabe

/ edit thanks for the answers, I am especially interested in examples that do not contain administrative things or database transformations or something like that, I am looking for examples where the code in the java example is more complicated than using a dynamic sql file, for example, stored procedures.

Answer 1

I once had to write an Excel import in which the excel sheet did not look like a csv file, but laid out like a matrix. Therefore, I had to deal with an unknown number of columns for three temporary tables (columns, rows, "infield"). Strings were also a short tree shape. Sounds weird, but it was fun. In SQL Server, it was not possible to handle this without dynamic SQL.

Answer 2

An example of dynamic SQL is fixing a faulty schema and using it more conveniently.

For example, if you have hundreds of users and someone initially decided to create a new table for each user, you might want to redo the database to have only one table. Then you will need to transfer all existing data to this new system.

You can query the information schema for table names with a specific name pattern or contain specific columns, then use dynamic SQL to select all the data from each of these tables, and then put them in one table.

INSERT INTO users (name, col1, col2) SELECT 'foo', col1, col2 FROM user_foo UNION ALL SELECT 'bar', col1, col2 FROM user_bar UNION ALL ... 

Then, hopefully, after that you won't need to access dynamic SQL again.

Answer 3

Once upon a time, I worked with appliaction when users use their own tables in a common database. Imagine that each user can create their own table in the database from the user interface. To access data from these tables, the developer needs to use dynamic SQL.

Answer 4

Another example from a situation that I recently encountered. The MySQL database contains about 250 tables, all in the MyISAM engine, as well as the database design diagram, diagram, or other explanation in general - well, except for the not very useful table and column names.

To plan the conversion to InnoDB and find possible foreign keys, we either had to manually check all the queries (and conditions used in the JOIN and WHERE clauses) created from the web interface code, or create a script that uses dynamic SQL and checks all combinations of columns with compatible data type and compares data stored in these column combinations (and then manually accepts or rejects these capabilities).