Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
If they have your access that locked down there isn't anything you can really do.
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.
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.
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.
bulk insert [dbo].[CRS_Header_Detail_C
from 'C:\Users\Public\Documents
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).
from 'C:\Users\Public\Documents
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).
Add FIRSTROW=2 to skip reading the header row.
with (fieldterminator = '|', rowterminator = '\n',KEEPNULLS, FIRSTROW=2)
with (fieldterminator = '|', rowterminator = '\n',KEEPNULLS, FIRSTROW=2)
Premium Content
You need an Expert Office subscription to comment.Start Free Trial