Saturday, December 6, 2008

Writing sqlcmd batch files to do bulkfile copies or imports

Writing sqlcmd batch files to do bulkfile copies or imports

I want to write a batch file that will do just that. The problem is bcp or bulkcopy never works with my code, it doesn't recognize it. Maybe I'm going the wrong way to do this, but I could use some help. Ideally I'll make the batch to do the importing via bulkcopy or something like it of a text file into a table and use windows scheduler to automate it. Be as specific as you can please, I'm very new to sql server.

To import text file to a SQL Table just use BCP IN with parameter. No need to use SQLCMD.

ALso post back your code

The code I went with was

BULK INSERT dbo.table FROM 'c:\file.txt' WITH (FIELDTERMINATOR = ',')

this worked for me.

Everything can be done with SSIS package. You can do the same thing as below.

1.) Create a new SSIS package and desgin it as per your requirement.
2.) Save the package as "Structured storage file" (eg. as .dtsx) in a path
3.) Use the below command line utility to execute the package in command prompt

dtexec /f "c:\Yourpackagename.dtsx"

Make sure to change the correct path & package name before using the above command

To know more about dtexec pls click here

1 comment:

Dinesh Dattatray Vishe said...

if i want sqlcmd result in MSSQl table then waht to do ??/