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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-02-09 : 09:40:07
|
| Patrick writes "I've been looking and searching this site for a reasonbale answer to a question I have:I am wanting to import several tables into a SQL Server 2000 which are written out from a PICK system to a directory on our unix box.We currently process the files using SP's which use BULK INSERT and FORMAT files. Here's the problem: The table schema may be different in the text file (the column headings are included in the first row of the file) and would cause our scheduled job to fail on the import (due to the FORMAT file being setup different then the file).What I want to do is create the "CREATE/ALTER TABLE" scripts for the tables based on the text file on the server, build those tables and then import the associated data.Is there a way to do this with VB6 through SQL-DMO? Or would it be better to do a combination of VB6, DMO and Stored Procedures?Any thoughts on this would be great. Looking for your return reply.Patrick Whitson" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-09 : 14:48:04
|
| Don't think there is anythuibg to do it for you but you could build up the script from the file if enough information is in it.You would also have to create the format file to match the table you are creating and the file format otherwise there doesn't seem to be much point.You could do it via dmo but it's probably easier to create a text file with the conditional drop and create statement then execute it via osql. In that way it's easy to develop because you can see the text file that is to be executed and you can use similar code to build the format file.You could also do everything in an sp - bcp the data in to a single column table then parse it, create the table and copy the data to it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
whitsonp
Starting Member
1 Post |
Posted - 2006-02-10 : 11:02:20
|
nr, Thanks for your reply. Sounds like doing it through a SP would be the way to go. Do you have an example of reading the file into a single column and then parsing it out? Is there a topic in this forum on that? Thanks again.Patrick Whitson  |
|
|
|
|
|
|
Building 'CREATE TABLE' Scripts Dynamically