创建列名列表以插入表中 [英] Create list of column names for insert into table

问题描述

我正在处理一个查询，在该查询中，我需要在具有相同列名的表和临时表中插入行.

I'm working on a query where I need to insert rows into a table and a temp table that have the same column names.

我想避免在每个插入语句中重复列名，而是希望创建一个列表并将该列表传递给插入语句.

I would like to avoid repeating the column names in each insert statement and would prefer to create a list and just pass the list to the insert statements.

这是我到目前为止所拥有的:

Here's what I have so far:

DECLARE @columnNameList varchar(MAX) = COALESCE(RecType, ColumnName, 
    SourcePosition, MaxWidth, isNullable, dotNetDataType, [Format])

EXEC('INSERT INTO #RowList (' + @columnNameList + ')
    VALUES......

该几乎有效.对于每个看起来像这样的列名，我都会得到一个错误:

This almost works. I get an error for each column name that looks like this:

无效的列名"RecType"

Invalid column name 'RecType'

我认为错误是因为在将列名转换为字符串时，列名周围有单引号.

I think the error is because the column name has single quotes around it as it is being converted to a string.

有没有一种方法来建立列对象列表而不是要传入的字符串?

Is there a way to build a list of column objects rather than strings to pass in?

推荐答案

在下面尝试此查询.

create table #rowlist (RecType int, ColumnName int, 
    SourcePosition int, MaxWidth int, isNullable int, dotNetDataType int, [Format] int)

DECLARE @columnNameList nvarchar(MAX) ='(RecType, ColumnName, 
    SourcePosition, MaxWidth, isNullable, dotNetDataType, [Format])'

DECLARE @SQLCMD nvarchar(MAX) =N'INSERT INTO #RowList ' + @columnNameList + N' VALUES (1,1,1,1,1,1,1)'

exec(@sqlcmd)

这篇关于创建列名列表以插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！