I am trying to upload data from CSV file into SQL Server which is 2GB in size and has more than 10000 columns. Please let me know how to load data with more than 1024 columns in SQL Server.
I tried to do through Import/Export wizard,it threw below error
Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE [dbo].[Test] ( [ID] varchar(50)..." failed with the following error:
"CREATE TABLE failed because column 'B19037Dm38' in table 'Test' exceeds the maximum of 1024 columns.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
You can create a "wide table" but that comes with its own limitations and restrictions. You probably won't be able to use the wizard for this; if you want an SSIS package to do it regularly (which is what the wizard is under the covers), you'll have to pull out Visual Studio and build one.
I would instead advise that you transform the data into a more normalized form with more tables and fewer columns per table. Or, consider the possibility that your data doesn't fit well into this model at all and instead use a "big data" platform like Hadoop or other NoSQL database.
Maybe you need to transpose your data and bring in your columns as rows and your rows as columns.
Or like alroc says you may want to split it up into multiple tables and share a key across those tables. 10 tables with 1024 columns, first table would have the primary key and all the other tables would have a foreign key to that table.
Also, not really knowing what your data looks like it's hard to comment further. Normalization through SSIS package might be your best bet.
