1

I have this code in C#, but I need it to select all columns EXCEPT the first column of the table (the identity column), so that when I insert the data into an identical table in a different database, the destination database assigns its own identity column values:

SqlCommand commandSourceData = new SqlCommand($"SELECT * FROM dbo.{tableName};", sourceConnection);
SqlDataReader reader = commandSourceData.ExecuteReader();

Is there a way to do this?

Josh Withee
  • 9,922
  • 3
  • 44
  • 62
  • Yeah, in the query, instead of using the `*` wildcard, you can specify the name of the columns that you need, like `select col1,col2,col3...` – Hackerman Dec 18 '17 at 17:16
  • It needs to be dynamic though so that I can pass it any table name and it will do it for that table – Josh Withee Dec 18 '17 at 17:19
  • 2
    What happens when your identity column is not the first one? You really should manage data explicitly. But if you are deadset on a generic catch all insert like this you would have to use dynamic sql and select only those columns that are not an identity and not a computed column. – Sean Lange Dec 18 '17 at 17:22
  • This is really, really dangerous as not only can your identity column not be "first" as Sean said, but also you can have tables without identity columns, encrypted columns, and you can have other columns that are meant to be populated directly (auditing columns, newsequentialid, temporal tables, rowversion, computed columns, etc). – Aaron Bertrand Dec 18 '17 at 17:38
  • Can you explain why you need to change these identity values? Probably you will break every relationship existing between these tables. – Steve Dec 18 '17 at 17:53
  • @Steve It is a small solution where data is being placed in a staging database before being put into a data warehouse. It will be used on tables that don't have foreign keys involved. – Josh Withee Dec 18 '17 at 17:55
  • OK, but if you really don't care about the identity values then you could also use "set identity_insert off" and then "on" without the need to exclude that identity column and keeping the original values – Steve Dec 18 '17 at 17:58
  • I got the impression that doesn't work: [6651809](https://stackoverflow.com/questions/6651809/sqlbulkcopy-insert-with-identity-column) – Josh Withee Dec 18 '17 at 18:04
  • Ah, well using SqlBulkCopy, I have not seen the tag. Then perhaps my answer could be of help – Steve Dec 18 '17 at 18:11
  • It looks like it's going to, I'm working on testing it out. – Josh Withee Dec 18 '17 at 18:18

1 Answers1

4

If you want a generic solution for every column in your database you can use this kind of code

public string GetColumnsWithoutIdentity(string tableName, SqlConnection con)
{
    SqlDataAdapter da = new SqlDataAdapter($"SELECT * FROM dbo.{tableName} where 1=0", con);
    DataTable dt = new DataTable();
    da.FillSchema(dt, SchemaType.Source);
    var cols = dt.Columns.Cast<DataColumn>().Where(x => !x.AutoIncrement).Select(x => x.ColumnName);
    return string.Join(",", cols);
}

Now you can use the returned string to build an Sql statement without the autoincrement column.
Notice that this code is vulnerable to Sql Injection. You should be absolutely sure that the tableName parameter used to build the first query is not typed directly by your user. Let it choose from a whitelist (readonly) of predefined tables (and also this is not 100% safe)

Another drawback is the fact that you need to hit the database two times. Once to get the schema with the info about the AutoIncrement column and one to fill the datatable after that.

Steve
  • 213,761
  • 22
  • 232
  • 286
  • If you wrap the tablename in QUOTENAME it would make this safe from sql injection but the whitelist is still a very appropriate thing here. Is there a way to also exclude a computed column like this? – Sean Lange Dec 18 '17 at 17:37
  • By _computed column_ do you mean a column whose _Expression_ property is not null or empty? – Steve Dec 18 '17 at 17:40
  • 1
    I suppose so. I don't know how that works on the dotnet side. There are a few other potential issues like encrypted columns and such that would be required to make the work safely....but the OP doesn't seem too concerned that this be a very flexible solution. – Sean Lange Dec 18 '17 at 17:47
  • Well, just the idea to change the identity values to something else is a bit suspicious. We should really try to understand what is the real reason behind his requirement. – Steve Dec 18 '17 at 17:52