SSIS Create table with INTO

Question

I would like to auto create a table using BIDS. How would I go about auto-making a new table when the data structure is unknown?

example in SQL I would do:

SELECT * INTO <NEW_TABLE> FROM <ORIGINAL_TABLE>

However, I get the data set. Transform it and want to create a table without knowing the structure.

Is there a way to do that?

You want to create a table based on the meatadata from the query but not fill with values? — billinkc, Jul 12 '13 at 16:29
Please note I am pulling from a secondary source (Non-MSSQL) and want to put the information in SQL using BIDS. Destination Database is MSSQL 2008R2 or MSSQL 2012. — Michael Kagarise, Jul 12 '13 at 16:50
Ah so you want to use the metadata from the source to define the target table in SQL Server? — billinkc, Jul 12 '13 at 16:57

score 0 · Answer 1 · answered Jul 12 '13 at 16:44

You can define the metadata for a table based on the results of a query, sort of. Some things like an identity property, unique constraint, defaults, primary key, etc will not be able to be determined but if you just want column names and data types but no data, then what you have is essentially what you need

SELECT *
INTO
    <NEW_TABLE>
FROM
    <ORIGINAL_TABLEs and/or many joins>
WHERE
    NULL = NULL

The where clause is the trick. Just use a condition that will never be true. The query will evaluate, you'll get all the column names that are defined and their types but no data will be populated in the new table.

You might be interested in wrapping this with an existence check. Either drop the table if it exists or don't create it if it already exists. I'm not sure what would be appropriate in your situation but the check would be

IF EXISTS (SELECT * FROM sys.tables T WHERE T.name = N'MyTable' AND T.schema_id = SCHEMA_ID('mySchema'))
BEGIN
    -- Take action here as the table exists (or not)
END

SSIS Create table with INTO

1 Answers1