Solved

SQL Import Record From A Text File

Posted on 2011-03-02
9
188 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 
LVL 40

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Encryption question 2 62
SQL Restore Script - Syntax Error 8 104
SQL Server: SNAPSHOT replication to include a newly added table. 2 30
SQL Error - Query 6 42
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

830 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