0

I use SQL Server Management Studio 2012 to import data from Excel sheet to a table on SQL Server. I use the most straightforward way that I just Ctrl+C a table in Excel and Ctrl+V in table edit window in SSMS. I know that many other ways faster exist to import data. But this is most simple without a need to write any code.

But it takes 300 rows with 5 columns about 5 minutes. Is there any way how to speed it up without using completely different way that would require copying CSV on server or writing a script? Why does it take so long?

closed as off-topic by Mark Sinkinson, Colin 't Hart, dezso, RolandoMySQLDBA, Vérace May 18 '15 at 18:03

If this question can be reworded to fit the rules in the help center, please edit the question.

  • This question does not appear to belong here - otherwise conflicts with the scope of site does not provide much insight to me, neither those links do... Where it actually belongs? – Vojtěch Dohnal Jun 15 '15 at 8:05
2

The answer is no, you cant speed this up. This is not a good way of dealing with importing data as there is quite a lot of code running in background creating insert scripts for the lines on the clipboard.

If you don't want to write the code: SELECT * INTO TABLE FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=<PATH W/ TRAILING\>;Extensions=CSV;','SELECT * FROM <FILE>')

Then you are IMHO better of inserting the data into SQL server through a linked table in Access than via the clipboard in SSMS.

  • The problem with the code is that I have to first copy the file somewhere on server, which I prefer not to do. Linked Access table is a good idea, gonna try it. – Vojtěch Dohnal May 18 '15 at 9:33
  • 1
    You can always create a file share, give the sql server service account read access to that share and use the string before DEFAULTDIR=\\server\share\ no need to copy the file and fast import. Same goes with the import/export wizard which can instruct the server to read data from a fileshare. – Spörri May 18 '15 at 9:53
  • Linked Access table works for me many times faster, thanks. – Vojtěch Dohnal May 18 '15 at 10:08
3

You don't need to write code. Just use the Data Import/Export Wizard.

Right-click on the database in SSMS, go to Tasks and Import Data. Select Flat File Source as your data source (because you're using CSV), and then browse for the file. Click next, select the database and the target table. You'll need to check the mappings for the CSV columns to the table columns (making sure the CSV column headings are the same as the table columns helps with this), and you can preview the data to make sure.

If everything is correct, click Next, Next and Finish to import the data. If there's an error, it'll tell you what it is (sometimes slightly cryptic though).

As a note though: I'd advise against trying to copy/paste/edit data in the Table Edit window. Writing SQL statements is a much more effective and time efficient way of doing it (or using the Data Import/Export Wizards).

  • Yes, you are probably right, but I like the simplicity of just few clicks and that I get what I see. – Vojtěch Dohnal May 18 '15 at 9:35
1

If you don't want to use the Import/Export Wizard, I'm one for using Excel's built in functions to easily create my SQL Scripts to import data from CSV.

One way to do this is with the CONCATENATE operator.

Imagine you are writing an INSERT....VALUES statement, you need to build up the VALUES part with data in your CSV. This is essentially a comma separated list of values, enclosed by parentheses.

enter image description here

Building a CONCATENATE like this gives you a basic set of values that you can use in the VALUES for your INSERT.

enter image description here

You can then click and drag the cell to replicate the formula across all lines of data.

enter image description here

Lastly, you copy and paste into SQL Server, replacing the last comma with a semi-colon.

INSERT INTO dbo.myTable (x, y, z)
VALUES
('a', 'b', 'c'),
('d', 'e', 'f'),
('g', 'h', 'i'),
('j', 'k', 'l');
  • In this case I need to get simple tabular data quickly (different tables I get from someone else) to SQL Server with as few clicks as possible. This is interesting, but I would have to click too many times. – Vojtěch Dohnal May 18 '15 at 9:51
  • 2
    'Have to click too many times'? If you're getting the tables from someone else, it would be easier via a Linked Server. – Mark Sinkinson May 18 '15 at 9:55
  • Sorry, meant worksheets not tables. – Vojtěch Dohnal May 18 '15 at 9:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.