I need to create a table at runtime in order to create a stock book. Column names are the Item codes. Initial table is created as follows,
'Create the new table
strSQL1 = "CREATE TABLE " & Trim(dbName) & ".dbo.TB_ST_BOOK (" &
"id int IDENTITY(1,1) PRIMARY KEY, " &
"Entry VARCHAR(30), " &
"entry_date DATETIME, " &
") "
obj.CommandText = strSQL1
obj.ExecuteNonQuery()
After that, I use a loop to create the rest of the columns using item codes which exists in another table.
'Add new columns as a loop
Dim tempTb As DataTable
Dim sqlString = "SELECT IT_CODE,IT_NAME FROM TB_ITEMS"
tempTb = myTbClass.myFunctionFetchTbData(sqlString)
For i = 0 To tempTb.Rows.Count - 1
strItCode = Trim(tempTb.Rows(i).Item("it_code")).ToString
strSQL = "ALTER TABLE TB_ST_BOOK ADD IT_" & strItCode & " VARCHAR(20)"
obj.CommandText = strSQL
obj.ExecuteNonQuery()
Next
My concern is what could happen if the number of columns exceed 1024 limit (when no of items are more than 1024)?. I read that creating a wide table will resolve the issue. If so, how can I modify the above code to create a wide table?. Will I have to change my Insert, update and delete codes once i change it to a wide table?
