0

I just tested importing 1321 records (one int column as a key, two text columns as nvarchar(100)) to a MS SQL server.

In Navicat this took me 7 seconds to create with the import wizard

In Datagrip it took 280ms per row (370 seconds). The method I chose to do this was:

1) Open .csv file 2) Use the SQL Insert and Data Extractor options 3) Rename MY_TABLE to the appropriate name (this caused lag on my system with 16gigs of RAM) 4) Control A and then execute

I saw it inserting each row one at a time. This is a simple lookup table. After this I am planning on importing records from 2014 until present (I am creating a new database) which consists of several million rows. Am I inserting .csv files incorrectly? What options do I have here?

trench
  • 5,075
  • 12
  • 50
  • 80
  • Not sure what step 2 means here. Is that something in Excel? I would think that using the data import wizard in sql server would be the best way to go. – Sean Lange Apr 06 '16 at 20:19
  • It covers the steps in datagrip https://www.youtube.com/watch?v=MtrimePA58U as per their official video. This is regarding a specific tool to manage SQL Server called Datagrip (same company that makes PyCharm). I currently am using SSMS and Navicat (trial). – trench Apr 06 '16 at 23:38
  • I don't know anything about this tool and I am not about to go watch videos to help here. Seems if that tool is really slow at importing data then perhaps it is not a good tool for the job. I hope you can figure out a solution. – Sean Lange Apr 07 '16 at 13:43
  • No worries. I'm trying to get someone who uses the tool to pitch in. I saw threads on reddit about it and I know Pycharm is popular so I know some people must be familiar – trench Apr 07 '16 at 14:48
  • Try DataGrip 2016.1 it might be faster – moscas Apr 25 '16 at 15:00
  • Just tried. It is still painfully slow. Probably because you need to copy everything to clipboard and then when you are trying to rename the table, for example, it selects all rows. I tried a bulk insert with an XML format file and it works really well, but the import from CSV just isn't any good. – trench Apr 25 '16 at 17:31
  • Check my answer, please: we improved it as you asked for. – moscas Jul 25 '16 at 11:19

1 Answers1

1
  1. Context menu of datasource you want to import to
  2. Choose Import from File....
  3. Customize the table that will be created, check the preview and press OK.

Context menu Import Dialog

moscas
  • 9,064
  • 36
  • 42