Select all columns except the first column for any given SQL Server table

c# sqlbulkcopy sql-server

Question

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?

Accepted Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why