Create a table in SQL Server based on a definition row in Excel WITHOUT using sampling

Question

I want to create a table in SQL Server and define the column names AND types in rows such as below - please READ ON after the picture. Like this:

I've already research and know of ways in which you just define COLUMNS in excel and under each column you give some sample values and SQL Server automatically creates a table with those columns, but the column types are "guessed" after sampling the first, let's say 100 rows (you can adjust the sampling size). This does NOT work for me. My table has a lot of variations (VarChar1, 2, 3, 5, or even CHAR). So I want to be able to define it using a description as shown above. Anyone has a solution? Thank you so much in advance!

Answer 1

In my solution, first you need to import you definition into a table.

Then you can generate a script and execute it to create the table.

-- Sample Data in Temp Table #tt
CREATE TABLE #tt (
    fieldname VARCHAR(50)
    , datatype VARCHAR(50)
    )

INSERT INTO #tt
VALUES ('FirstName', 'varchar(20)'), ('LastName', 'varchar(20)'), ('Age', 'int')

-- Generate Script
DECLARE @sqlText VARCHAR(max) = (
        SELECT 'CREATE TABLE YourTableName (' + CHAR(13) + STUFF((
                    SELECT ', ' + fieldname + ' ' + datatype + '$$'
                    FROM #tt
                    --ORDER BY fieldname --commented out
                    FOR XML PATH('')
                    ), 1, 1, '') + ')'
        )

SET @sqlText = replace(@sqlText, '$$', CHAR(13))

PRINT @sqlText

-- Execute
EXEC (@sqlText)

In my example the generated script will be

CREATE TABLE YourTableName (
 Age int
, FirstName varchar(20)
, LastName varchar(20)
)