March 1, 2012 at 1:12 pm
Hi All,
I have a data in a .txt file.But i want to add that data in to a SQL server Table.
Before add data to table, i need to match the data type of column with data in
.txt file.if data of text file is not match with SQL server table column,Error should be generated.
Please share your thoughts.
Thanks in Advance
JKP2311
March 1, 2012 at 2:32 pm
I assume you know the format of the data in the file, and data type expected in each column.
Therefore I would create a staging table in SQL server, having columns named, data types and ordered appropriately. Then use Bulk Insert (or bcp) to insert the data into the stage table. The bulk insert will fail if there is inconsistencies in the data and you can then take appropriate action.
The probability of survival is inversely proportional to the angle of arrival.
March 1, 2012 at 2:44 pm
Hi Sturner,
I don't have permission to use the Bulk Load Statement.
Is there any other way to load the data in to staging table?
Please, let me know.
Thanks,
Jkp2311
March 1, 2012 at 2:49 pm
You can use bcp. Use BOL and see how to use it, it is a command line utility.
The probability of survival is inversely proportional to the angle of arrival.
March 4, 2012 at 2:51 pm
Your bulk insert doesn't need to go into the production database directly. i.e. You can ask your admin to create a separate 'staging' database on the same server as your target database dedicated to the purpose of loading staging information. (S)he will most likely not have much problems with assigning you sufficient rights for a bulk insert into this 'staging' database only.
Once you've got the data loaded into your 'staging' database you can use a procedure with only 'normal' commands to merge the new data into the real database. You will need only execute rights on this procedure. The admin can verify and test the procedure used to do the merge, so that should give him/her sufficient control over what happens in/to the production database.
March 5, 2012 at 6:42 am
Hi Rozema,
I most likely to use your scenario as you told me that.
You need to create the staging table and develop the one store procedure.
Then load the data into the Staging table.
Can you tell me what t-sql query i need to use to load the data except the
bulk insert.
Thanks for your feedback
March 5, 2012 at 7:51 am
Have a look at openrowset(). This can be used to read from a file on the server's file system (if the admin permits it. If not you'll get a lot of error 5 - access denied).
For example to read the entire contents of a text file into a variable you can use the following:
declare @txt nvarchar(max);
select @txt = a.BulkColumn
from openrowset ( bulk N'c:\test\test.txt', single_blob) as a; As I said, the file needs to be on the sql server's c: disk (or whatever path you specify). When specifying a location for a document to read always remember that sql server is client-server: you instruct the server to read the document, it's not your machine doing the file reading...
With the correct permissions set up you can also make the server read from -for example- a network share. Your admin may want to research "Kerberos constrained delegation", as this provides an excellent method to give individual users/logins permission to perform such actions instead of assigning permissions to the server process that may be (mis-)used by any client. Using Kerberos constrained delegation any user will only be able to make the server read from a location the user can read from himself (provided he has been assigned permissions to use the method at all of course). i.e. it works well together with any existing security plans. More info: link and link.
The better known way to import data is to use SSIS of course...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply