在SQL Server中基于Excel中的定义行创建一个表，而不使用抽样

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:

我想在SQL Server中创建一个表，并定义列名和类型，如下面所示——请在图片之后继续阅读。是这样的:

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!

我已经研究和知道你的方式定义列在excel中,每一列下你给一些样本值和SQL服务器自动创建一个表的列,但列类型抽样后第一个“猜”,比方说100行(你可以调整取样大小)。这对我不起作用。我的表有很多变化(VarChar1、2、3、5甚至CHAR)。我想用上面所示的描述来定义它。谁有解决方案吗?非常感谢!

1 个解决方案

#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)
)

#1