Solved

SQL Import Record From A Text File

Posted on 2011-03-02
9
186 Views
Last Modified: 2012-05-11
I extracted a record from the DEV Dbase.  I need to import into the QA Dbase.  How do I import the record from a text file?

0
Comment
Question by:CipherIS
9 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35021691
Bulk insert the data from text file.
BULK INSERT your_table FROM 'C:\TxtFile.txt' WITH (FIELDTERMINATOR = ' |',,ROWTERMINATOR =' |\n')

Open in new window

http://msdn.microsoft.com/en-us/library/ms188365.aspx
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35021746

Or you can use the import wizard,
In SSMS, right click on the database you want to import data into, select All Tasks-> Import Data and follow the wizard
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35021788
Do you have Dev and QA databases on same server or different servers?
0
 
LVL 1

Author Comment

by:CipherIS
ID: 35021821
1.  Don't have permission to use Bulk Insert.
2.  Dbases are on Diff Servers.
3.  Data in one column has comma's in it so SSIS is not working well.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 39

Expert Comment

by:lcohan
ID: 35022124
If is a csv type file you could use : Q306397 HOWTO: Use Excel with SQL Server Linked Servers and Distributed Queries

http://support.microsoft.com/kb/306397
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35022501
You can try any of these options - http://www.mssqltips.com/tip.asp?tip=1207
0
 
LVL 4

Expert Comment

by:samijsr
ID: 35025500
If you have permission to Run Ad Hoc Query then Save your Text file in Excel format and
Insert trhtough Sq=elect statement, make sure that number of columns are same and datatype matched.

Insert Into Table1
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'Excel 8.0;Database=c:\book1.xls', Sheet1$)

Ususally Ad HOC Query is Off so set Surface Area configuration to 1 and then Run the above Query
0
 
LVL 1

Accepted Solution

by:
CipherIS earned 0 total points
ID: 35180547
The resolution was to write a SQL BCP script
0
 
LVL 1

Author Closing Comment

by:CipherIS
ID: 35221133
Figured it out
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now