I’m trying to import some *.tsv files to Microsoft SQL Server database. To be more precise, I’m trying to import the IMDb Datasets.
In the SQL Server Management Studio I’ve tried to
right click my database > Tasks > Import Flat File…
When tried to import title.ratings.tsv like this, it worked. But when I tried to import name.basics.tsv, it failed with error:
TITLE: Import Flat File
An error occurred while running learn on the input file.
ADDITIONAL INFORMATION: Exception of type ‘System.OutOfMemoryException’ was thrown. (mscorlib)
The name.basics.tsv is much larger (806 882 kB) than the title.ratings.tsv (24 230 kB), so I suppose that is causing the problem.
I have already tried to increase the SET_TEXTSIZE (*2, then tried *4) as it was suggested in this answer, however it seems the SQL Server Management Studio won’t let me increase the number any further (it returned it to the same value as before).
Is there a way how to comfortably import a large *.tsv files (such as name.basics.tsv) into Microsoft SQL Server database? (Preferably without creating CREATE TABLE scripts where we would have to specify the schema, types. Because by using the mentioned method we wouldn’t have to.)
1
Use the more complex, but more scalable “Import Data…” wizard instead of the “Import Flat File”. It’s documented here: https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard?view=sql-server-ver16
You’ll want to use the .NET Framework connector for SQL Server as the destination, and you’ll probably want to use Integrated Security and Trust Server Certificate. And all the columns in that file should be loaded into varchar(200) columns.