Here’s a quick code to generate a “Create Table” query based on your Datatable object. The code is written on C# and it generates a create query for SQL table.

I had found this code somewhere on the StackOverflow site and it has proven to be extremely helpful.

 public static string GenerateCreateTableQueryFromDataTable(string tableName, DataTable table)
 {
 var sqlsc = "CREATE TABLE " + tableName + "(";
 for (int i = 0; i < table.Columns.Count; i++)
 {
 sqlsc += "\n [" + table.Columns[i].ColumnName + "] ";
 string columnType = table.Columns[i].DataType.ToString();
 switch (columnType)
 {
 case "System.Int32":
 sqlsc += " int ";
 break;
 case "System.Int64":
 sqlsc += " bigint ";
 break;
 case "System.Int16":
 sqlsc += " smallint";
 break;
 case "System.Byte":
 sqlsc += " tinyint";
 break;
 case "System.Decimal":
 sqlsc += " decimal ";
 break;
 case "System.DateTime":
 sqlsc += " datetime ";
 break;
 case "System.String":
 default:
 sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());
 break;
 }
 if (table.Columns[i].AutoIncrement)
 sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
 if (!table.Columns[i].AllowDBNull)
 sqlsc += " NOT NULL ";
 sqlsc += ",";
 }
 return sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";
 }
Last modified: September 14, 2018

Author