July 12, 2010 at 11:31 am
Hi
I'm trying to import data from text files in database and I want to schedule a BULK INSERT job to do that. I want that I should be able to drop that files in a folder and when I execute the job or schedule the job, it should automatically import the new file into database. Any Ideas to do that?
Thanks
July 12, 2010 at 12:03 pm
Yep, not a problem. SQL Agent is the built in scheduling tool for SQL Server. Create a job there, you can create one or more schedules for the job and run it pretty much any way you want to.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 12, 2010 at 12:22 pm
Hi,
Since the file name and the columns changes everytime, So it will only run for one time. So how do I load multiple files each into different tables?
Thanks
July 12, 2010 at 12:30 pm
Novicejatt (7/12/2010)
Hi,Since the file name and the columns changes everytime, So it will only run for one time. So how do I load multiple files each into different tables?
Thanks
That's not a scheduling problem, that's a design problem. Constantly changing columns... you'll have to define what you mean by that in order for me to suggest a solution. Basically, I'd have to say that you need to create a new import table every time, but that's going to be a pain. Details are needed.
As far as the file name changing... again, this could be an issue or not, depending on what you're trying to do. Let's say you have a particular type of file, it comes in, whenever. You run a SQL Server Integration process on a nightly basis (scheduled through SQL Agent). It checks a particular directory for the existance of any new files. If they are there, it runs the import routine (details, tbd) and then, when successfully complete with the import, moves to the files to a "done-did" folder to mark completions.
Multiple files into different tables... basically, not an issue again, but you can't just say any file goes to any table at any time in any way. You need to establish structure. Files that start with X go to table X or files that start with Y always get a new table, that sort of thing. Some structure around the approach and then it just becomes a processing job.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 12, 2010 at 1:16 pm
Hi
I do not want to use SSIS yet. I'm only working with SQL queries. Say I have a table with fixed no. of columns. I want to schedule a job that imports data into this table. How do i append to an existing table. When I BULK INSERT new file into exixting table, all the column name get added too. I just want to add data coz I already have column names in the top row of the table. Also since the file that comes every day has different name each time, so how do I do it without changing the file name manually.
Thanks
July 13, 2010 at 5:26 am
Novicejatt (7/12/2010)
HiI do not want to use SSIS yet. I'm only working with SQL queries. Say I have a table with fixed no. of columns. I want to schedule a job that imports data into this table. How do i append to an existing table. When I BULK INSERT new file into exixting table, all the column name get added too. I just want to add data coz I already have column names in the top row of the table. Also since the file that comes every day has different name each time, so how do I do it without changing the file name manually.
Thanks
If the column names are getting added you need to check the format of the file and the definition used in the BULK INSERT command. You should be able to avoid that with no issues. Also, you shouldn't have the column names "in the top row of the table." The column names are part of the table definition and the columns from there only contain data. Anything else will lead to a lot of confusion.
The file names changing daily, I'm assuming you have a naming standard so that the file is named something like ImporThis_7_13_2010.txt. If so, you just have to build a little bit of logic into your TSQL command so that you can create a file name on your own that matches. Something along the lines of:
DECLARE @filename nvarchar(50)
DECLARE @currdate date
SET @currdate = GETDATE()
SET @filename = 'ImportThis_' + CAST(DATEPART(mm,@currdate) AS VARCHAR) + '_' + CAST(DATEPART(dd,@currdate) AS VARCHAR) + '_' + CAST(DATEPART(yy,@currdate) AS VARCHAR) + '.txt'
SELECT @filename
If your file names are just random strings, you can't do this. There has to be structure and process in order for you to supply automation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 13, 2010 at 6:19 pm
Hi,
I can Arhive the files in a datewise format. But Can you explain this a little more how to use this with BULK INSERT.
I also found a solution by using XP_CMDSHELL and BULK INSERT to import all files from a given folder. Which wud be better?
Thanks
July 13, 2010 at 6:59 pm
Novicejatt (7/13/2010)
Hi,I can Arhive the files in a datewise format. But Can you explain this a little more how to use this with BULK INSERT.
I also found a solution by using XP_CMDSHELL and BULK INSERT to import all files from a given folder. Which wud be better?
Thanks
I would not suggest using XP_CMDSHELL. In fact, I'd suggest keeping it disabled on your production machines unless you really need to. It's something of a security hole.
Honestly, if you're trying to process lots of files, why not go with SSIS? It's going to do a much better job, much quicker, than anything you can put together through TSQL. Heck, I'd rather use PowerShell than do all that kind of processing through TSQL.
As to BULK INSERT, what problems are you having specifically?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2010 at 11:39 am
If I use BULK INSERT as such, then it only imports one file into the table. I want that I should just drop in the text files(having random names or as you said earlier that the files names must have a naming standard) in a folder and write a query that checks for for any new files and automatically imports the files into database.
You gave me the Idea before by using naming standards, but can you explain it to me more detail with the code how that will work with BULK INSERT.
Thanks
July 14, 2010 at 11:54 am
Novicejatt (7/14/2010)
If I use BULK INSERT as such, then it only imports one file into the table. I want that I should just drop in the text files(having random names or as you said earlier that the files names must have a naming standard) in a folder and write a query that checks for for any new files and automatically imports the files into database.You gave me the Idea before by using naming standards, but can you explain it to me more detail with the code how that will work with BULK INSERT.
Thanks
There really isn't a way to do this using only TSQL. You will have to bring some other functionality into play because TSQL can't manipulate the files (clean them up after you're done with the import), only read from them.
As I said in my last post, you'd be better off looking at SSIS or PowerShell or some other programming or scripting language to run in conjunction with TSQL in order to pull this off.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2010 at 12:16 pm
So there isn't any way to import say multiple tab or comma delimited text files (A.txt,B.txt,C.txt) using T-SQL query at one time? I'm not worrying about manipulation yet, just importing.
July 14, 2010 at 12:41 pm
Novicejatt (7/14/2010)
So there isn't any way to import say multiple tab or comma delimited text files (A.txt,B.txt,C.txt) using T-SQL query at one time? I'm not worrying about manipulation yet, just importing.
There are constructs you could use to try to sort of mirror the idea of walking through the files in the folder, but there's no way, using straight TSQL, to query to folder to identify which files are actually there and then begin to address them, as individuals. The only thing you could do is define a naming standard for the files, more even than what I suggested before, and then work off the assumption that you will always have X number of files.
TSQL is all about data manipulation. It's not a full-fledged programming language. You're trying to move into an area that it's just not well-suited for. Note, I didn't say you couldn't do it. I'm saying to that it's going to be a ton of work to do it and it won't ever quite work like you want. Whereas, you could pop open PowerShell and do the exact thing you want, with a great deal of ease.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2010 at 1:07 pm
Thank you very much.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply