briancampbellmcad (1/16/2013)
Yes using "opening a CSV file in Excel, copying the contents, then pasting directly into a table via "Edit top 200 rows" in SSMS"... which works well for about 19,500 out of 20,400 rows... I can find nothing unique or special about the rows that fail... always the same rows on repeat attempts... scanned the .csv visually and counted the longest fields and found none that exceeded the limit of my SQL Server fields.
As Gus said that will cause issues. Honestly you would be far better using excel to generate insert scripts instead of using the edit top 200 rows "feature". I have heard nothing but horror stories of that.
To leverage Excel to generate your data you can add a new column and use the CONCATENATE function in excel.
Say your data is in columns A - D you can add in column E (you may have to fiddle with the ' to suit your datatypes)
=CONCATENATE("Select '", Col1, "', '", Col2, "', '", Col3, "', '", Col4, "' union all")
Then use the copy cell feature to copy that function down the list. Copy the entire column and paste it into a query window.
At the top add "Insert MyTable (Col1, Col2, Col3, Col4)"
Then go to the last row and remove the union all.
Voila! In 30 seconds you created a massive insert statement that will work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/