Importing a pipe delimited text file into table using sql not wizard

Sql server 2008r2

Can anyone point me to a good example of
importing(insert) a pipe delimited text file in to a table.

Not using the import wizard please.
and i cant use Bulk insert
approx 12 million records

Thanks
fordraiderds
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Doug BishopDatabase DeveloperCommented:
Why not the wizard or bulk insert. They certainly can handle 12M rows. You can use SSIS, which is basically what the wizard does (it creates an SSIS package). I suppose the only other option is using bcp.
FordraidersAuthor Commented:
because i do not have rights to use Bulk insert..
Doug BishopDatabase DeveloperCommented:
Can you use bcp? It is a command line utility for importing data.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Try Now
FordraidersAuthor Commented:
No, i need just to use a INSERT text file from  a location on c: drive.
Doug BishopDatabase DeveloperCommented:
We'll if you can't it's bcp, bulk insert, the wizard (which means you can't use ssis), is say your only option is to make the file available to a DBA who has access and have them in the file for you.
If they have your access that locked down there isn't anything you can really do.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You would need external tools (Database Browser), for example) or scripts (e.g. PowerShell code) to go without. Both are implementing single row INSERT statements, and hence are slower than the bulk tools.
Doug BishopDatabase DeveloperCommented:
My thoughts are, if they don't have access to bcp, bulk insert, the import wizard, they are probably locked out of being able to access the server/database through PowerShell or other 3rd party tools.
I've worked as a database developer in shops where I've needed to get DBAs involved to do one-time imports into a production environment. Most production ETL environments run under a service account that has access to tools to do the import and export of files.
FordraidersAuthor Commented:
bulk insert [dbo].[CRS_Header_Detail_Current_Staging_024]
from 'C:\Users\Public\Documents\Data dump Performance Report 10DEC2018.txt'
with (fieldterminator = '|', rowterminator = '\n',KEEPNULLS)
go

got bulk insert rights..
but now error:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (PROJECT_SUBMIT_DATE).
FordraidersAuthor Commented:
Doug BishopDatabase DeveloperCommented:
Add FIRSTROW=2 to skip reading the header row.
with (fieldterminator = '|', rowterminator = '\n',KEEPNULLS, FIRSTROW=2)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Doug BishopDatabase DeveloperCommented:
Also, it appears that the row terminator is actually CR/LF which should be \r\n instead of just \n. It will work with just the \n but the carriage return would be imported in as part of UPDATEDBY, so instead of br1tim, it would actually come in as br1tm\r.
FordraidersAuthor Commented:
thanks doug/all  very much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.