Avatar of Fordraiders
Fordraiders
Flag for United States of America asked on

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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Fordraiders

8/22/2022 - Mon
D B

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.
Fordraiders

ASKER
because i do not have rights to use Bulk insert..
D B

Can you use bcp? It is a command line utility for importing data.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Fordraiders

ASKER
No, i need just to use a INSERT text file from  a location on c: drive.
D B

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

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
D B

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.
Fordraiders

ASKER
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).
Fordraiders

ASKER
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
D B

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
D B

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.
Fordraiders

ASKER
thanks doug/all  very much!