Determine datatypes of columns - SQL selection

Question

Is it possible to determine the type of data of each column after a SQL selection, based on received results? I know it is possible though information_schema.columns, but the data I receive comes from multiple tables and is joint together and the data is renamed. Besides that, I'm not able to see or use this query or execute other queries myself.

My job is to store this received data in another table, but without knowing beforehand what I will receive. I'm obviously able to check for example if a certain column contains numbers or text, but not if it is originally stored as a TINYINT(1) or a BIGINT(128). How to approach this? To clarify, it is alright if the data-types of the columns of the source and destination aren't entirely the same, but I don't want to reserve too much space beforehand (or too less for that matter).

As I'm typing, I realize I'm formulation the question wrong. What would be the best approach to handle described situation? I thought about altering tables on the run (e.g. increasing size if needed), but that seems a bit, well, wrong and not the proper way.

Thanks

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. — , Feb 22 '18 at 18:06

score 0 · Answer 1 · answered Feb 22 '18 at 17:08

Can you issue the following query about your new table after you create it?

SELECT *
INTO JoinedQueryResults
FROM TableA AS A
    INNER JOIN TableB AS B ON A.ID = B.ID


SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'JoinedQueryResults'

Is the query too big to run before knowing how big the results will be? Get a idea of how many rows it may return, but the trick with queries with joins is to group on the columns you are joining on, to help your estimate return more quickly. Here's of an example of just returning a row count from the query above which would have created the JoinedQueryResults table above.

SELECT SUM(A.NumRows * B.NumRows)
FROM (SELECT ID, COUNT(*) AS NumRows 
      FROM TableA 
      GROUP BY ID) AS A 
    INNER JOIN (SELECT ID, COUNT(*) AS NumRows 
                FROM TableB 
                GROUP BY ID) AS B ON A.ID = B.ID

The query above will run faster if all you need is a record count to help you estimate a size.

Also try instantiating a table for your results with a query like this.

SELECT TOP 0 *
INTO JoinedQueryResults
FROM TableA AS A
    INNER JOIN TableB AS B ON A.ID = B.ID

Determine datatypes of columns - SQL selection

1 Answers1