Import source data files into sql server table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Import source data files into sql server table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-25 : 07:58:04

sonny writes "Hi Team

I have a problem here.. I need to load source data(flat) files into SQL SERVER table. But before loading them I want to read and store files infomation such as date, time, name and name extention so i can choose which file I need to load or which one not..

secondly, How can I pass more than one parameter and dynamically (please see example where passing parameter with store procedure)

Thirdly, How can and which way I can call more than one subroutine in case load tha data in different table in database.

example:
I have already made the batch script for loading file include run store procedure from batch along with passing parameter for store procedure..

*******************************************************
@echo off
echo.
echo.
echo ---------Start Import----------
echo.FOR %%f IN ("C:\sonnysingh\First\data\*") DO call :SourceIMPORT %%f First
echo.FOR %%f IN ("C:\sonnysingh\First\data1\*") DO call :SourceIMPORT %%f First
echo.osql -Uusername -Ppassword -Sservername -ddatabasename -Q"LoadSourcedata '20-02-2005','First'" -r1
echo ---------END Import------------
echo.
echo.
:SourceIMPORT
set Filename=%1
echo %filename%
BCP dtabasename..sourcedata in %Filename% -Sservername -Uusername -Ppassword -fC:\sonnysingh\First\bcp_format.fmt
echo Processing File ...%Filename% >> C:\sourceLogs\data_logs.txt
*****************************************************************

Thanks in advance
sonny
singhsonny@hotmail.com"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-27 : 10:44:57

First part:

CREATE TABLE FileInfo(FileName varchar(255) not null, FileSize bigint not null, FileDate smalldatetime not null, FileExt varchar(4) not null)
EXEC master..xp_cmdshell 'for %a in (c:\myfolder\*.*) do @echo %a,%~za,%~ta,%~xa >>c:\filedata.txt'
BULK INSERT FileInfo FROM 'c:\filedata.txt' WITH (FIELDTERMINATOR=',')

The first line creates a table for file data. The second runs the for command to enumerate all the files in the directory (underlined part). Change that directory to match the one you're using. It parses out file name, size, date, and extension and copies it to a text file (second underlined part). The third line bulk inserts that data into your table.

Second part:

You can use batch file parameters for the stored procedure parameters just like you're using for the bcp section. Change the line to:

echo.osql -Uusername -Ppassword -Sservername -ddatabasename -Q"LoadSourcedata '%2','%3'" -r1

And you can call your batch file like this:

mybatch filename 20-02-2005 First

The filename is already used for %1, but the date would go into %2 and First into %3. Technically you don't need to do set filename=%1, you could just put %1 in the bcp call.

Third part:

You can make the table name dynamic in the same way the filename is. Without knowing the logic required I can't give more detail, but you can look here for an example:

http://weblogs.sqlteam.com/robv/articles/4102.aspx

That shows how to use the for command to parse a text file's contents into multiple tokens. You could set up a text file to contain a file name and a table name, for example, then modify your for loop to include both on the bcp command line. You wouldn't even need a subroutine.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources