1

I have a situation where I want to import data from an external source which I don't know the schema at design time, this comes into my function as a DataSet.

I want to import a table from the dataset into SQLite, ideally I'd like to write something like this:

using (var connection = new SQLiteConnection("Data Source=temp.db")) {
    var command = connection.CreateCommand();
    command.CommandText = @"CREATE TABLE tempTable AS SELECT * FROM @tvp";
    SQLiteParameter param = command.Parameters.AddWithValue("tvp", importedData.Tables[0]);

    command.ExecuteNonQuery();
}

The code above throws an error saying there's a syntax error near @tvp which I wasn't able to get rid of, does SQLite not support the table valued parameter or am I missing some syntax? Is there a practical way to accomplish this?

Eduardo Wada
  • 2,606
  • 19
  • 31
  • 1
    even if it did: table-valued-parameter types usually need to be defined at the server *anyway*, so you'd need to know the schema to define the table-valued-parameter type; pretty sure it doesn't, though; I would say "perhaps bulk copy", but [sqlite doesn't do that either](https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/bulk-insert) – Marc Gravell Nov 09 '21 at 15:39
  • 2
    Does this answer your question? [Can I use parameters for the table name in sqlite3?](https://stackoverflow.com/questions/5870284/can-i-use-parameters-for-the-table-name-in-sqlite3) – Mark Benningfield Nov 09 '21 at 15:41
  • @MarcGravell According to this [answer](https://stackoverflow.com/a/1348769/1102585) that query works in SQL 2008, I just tested in 2019 and indeed it throws an error requiring the type to be created in the server – Eduardo Wada Nov 09 '21 at 16:09
  • @MarkBenningfield It doesn't answer the original question on table valued parameters (which I'd like to know anyway) but it does show what I'm trying to do is not supported. – Eduardo Wada Nov 09 '21 at 16:13
  • I completely disagree with the vote to close the question though, I was not able to find a clear answer to table valued parameter support on SQLite and the comments posted even though they do solve my particular problem, they don't answer whether or not table valued parameters are supported, which is something others will visit this question for in the future. – Eduardo Wada Nov 09 '21 at 16:20
  • @EduardoWada for that case, that answer should be a comment or a new answer in the original question, not in one duplicate. The thing is try to clean S.O. for search precision – Leandro Bardelli Nov 09 '21 at 17:19
  • The question marked as duplicate is definitely not a duplicate, a table valued parameter is a parameter that contains a table for its value, the supposed duplicate asks if a string parameter can be used as a table name – Eduardo Wada Nov 10 '21 at 09:29
  • 1
    @LeandroBardelli Which answer? The reference to the supposed duplicate is already a comment and the only answer to this question is already significantly different from what's contained in the duplicate, honestly, I don't see any other way of someone marking these questions as duplicates unless they don't know what a table-valued parameter is, and I don't think this type of explanation should be part of the question. – Eduardo Wada Nov 10 '21 at 14:26
  • @EduardoWada you should discuss this in meta. – Leandro Bardelli Nov 10 '21 at 15:27
  • In any case I don't vote down you, so check that a lot of people thinks in the same way. – Leandro Bardelli Nov 10 '21 at 15:29

1 Answers1

3

No, it does not.

As you can see from the documentation, there is simply no mechanism to bind a table to a parameter.

You can work around this by creating a temporary table and inserting into it.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • What is differs this answer to the duplicated reference accepted answer? – Leandro Bardelli Nov 10 '21 at 15:26
  • 1
    Marked duplicate is really about how to pass a *table name* as a parameter, such as `SELECT * FROM ?`, where the name is an existing table. This question is about how to pass in a *whole table's worth of data* (a table-valued parameter) – Charlieface Nov 10 '21 at 15:30