![]() ![]() The Load.dat is almost identical, except '"exec "'ĭELETE FROM. Sqlcmd -S "london-sql\sqlexpress" -E -Q "exec " bat file to run an SProc, and viola, it does what I tell it to do.įor the benefit of others, here's the Delete.bat Be certain you understand the differences when you choose. Your DB server have direct access to the files (which involves permissions).Īnd yes, truncate can be preferable to delete. Keep in mind that this approach requires that There is a discussion in BOL that compares bcp and bulk insert to help you decide which is better for your purpose. You use bulk insert, you don't need to shell out to a command line via xp_cmdshell. Therefore, it makes no sense to try to use it to delete rows - deleting rows does not move data into or out of the database. Since you are only importing data, you could use the bulk insert statement to import the files (rather than BCP). BCP simply moves data into or out of the database. That is the approach that you seem to desire, but your understanding of the sql server tools is contributing to the problem. The other alternative is to write tsql to do something similar. Notice how I completely ignored error handling - something you will likely need to do. To the comment about being interactive / flexible. Notice how I embedded the table names, file names, and server names in the file. To read the documentation for bcp and sqlcmd to understand the command line arguments, how they are used, and which you need to use. Once you get that working, you can then schedule it. The following ones use bcp to import data from a file into one of those tables. The first line simply runs sqlcmd and deletes all rows from 4 tables. Sqlcmd -Q "delete dbo.Test_Case delete dbo.zippy delete dbo.table3 delete dbo.results " -S servername -d London Just taking the most simplistic approach, you could write a DOS batch file Will these 4 files exist in the same place with the same names everytime this process runs? Do you want to prevent running this process if the files are not current or have already been loaded? I ask because the more you try toĪutomate this and the approach you choose will make these types of decisions more or less difficult (and error handling is difficult enough with simple scripting techniques). I'll backup - the first thing to consider is how flexible (or configurable or sensitive to runtime information) you want ![]() The first thing to consider is how database-centric you want this to be. Of express has other limitations that will become barriers later on.įor now, I'll run with the task scheduler approach. Obviously those are not features of every edition - so before you start down a path consider what editions of sql server you want to use/support. Is usually something done with SSIS and/or the sql server agent. Secondly, you need to decide (or at least make apparent to your readers) what resources you have available to you. Trying to use it to delete rows will not work - at least not directly. First, bcp is used to import or export data. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |