如何在sql命令文本中使用变量（datatable）来执行

I am new to Vertica and trying this in angular aspx page.

我是Vertica的新手，在angular aspx页面中尝试这个。

           `con.Open();
            cmd = con.CreateCommand();
            cmd.Connection = con;
            cmd.Parameters.Add(new VerticaParameter("@tblCustomers", table));
            cmd.CommandText = "insert into customers select * from @tblCustomers";
            cmd.ExecuteNonQuery();`

I have established the connection and inserted some fresh records too. But Now I am trying to insert bulk records in my vertica database's table.

我已经建立了连接并插入了一些新的记录。但是现在我试图在我的vertica数据库表中插入批量记录。

Something Same like SqlServer,

像SqlServer一样的东西，

I have loaded my table data into "table" variable. Which is a datatable.

我已将表数据加载到“table”变量中。哪个是数据表。

Is it possible to do like this ?? As I am getting some error

有可能这样做吗？因为我收到一些错误

" Incorrect syntax at or near $1"

“$ 1或附近的语法不正确”

customers and @tblCustomers both have same columns.

customers和@tblCustomers都有相同的列。

Thanks!

谢谢！

1 个解决方案

#1

Setting aside whether or not you should do something like this, in the code that you've posted you're passing @tblCustomers as a parameter to the query, so it's going to treat it as a string value, not an object name in the query. You need to build the CommandText in your code without that as a parameter. Something like:

抛开你是否应该做这样的事情，在你发布的代码中你将@tblCustomers作为参数传递给查询，所以它将把它当作字符串值，而不是对象名称。查询。您需要在代码中构建CommandText而不将其作为参数。就像是：

cmd.CommandText = "insert into customers select * from " & tableName

(Sorry if that syntax isn't quite right, but hopefully it gets across the point)

（对不起，如果这个语法不太正确，但希望它能够跨越这一点）

Some additional (and important) notes though:

一些额外的（和重要的）注意事项：

Always use a column list when doing an INSERT. Use INSERT INTO MyTable (some_column, some_other_column) SELECT... not INSERT INTO MyTable SELECT...
执行INSERT时始终使用列列表。使用INSERT INTO MyTable（some_column，some_other_column）SELECT ...不要INSERT INTO MyTable SELECT ...
NEVER use SELECT *. List out your column names.
切勿使用SELECT *。列出你的列名。

#1

cmd.CommandText = "insert into customers select * from " & tableName