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 |
|
paul2010
Starting Member
2 Posts |
 Posted - 2005-02-18 : 11:35:07
|
| Hi,Does DTS allow an automated way for inserts/updates in various related tables in a SQL Server database from a source file consisting of Master data (csv flat file) based specifically on database schema, i.e. table relationships, and constraints, etc.?Thanks,Paul |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-18 : 11:54:57
|
| No, you will have to create that logic in the package yourself.Better still, use BULK INSERT to import the data into a staging table, then run SQL commands to insert/update the various tables in the database from that staging table.rockmoose |
 |
|
|
paul2010
Starting Member
2 Posts |
Posted - 2005-02-18 : 12:09:54
|
| Hi,I understand the logic of staging database, but issue here is the target database has complex parent/child relationships along with multiple constraints. While preparing mapping logic (business rules) one does not know and have full knowledge of the target database complexity. I am wondering if DTS allows you to automatically read the DB schema and use it for insert/updates of appropriate fields. So, once I insert/update a parent field, it should automatically know what the dependent fields are and perform insert/updates accordingly.How can this be accomplised in an efficient and automated way?Thanks,Paul |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-18 : 12:45:38
|
| The programmer/designer must tell the system the business rules, and what goes where.There is no tool that will do this automatically for You.DTS has concepts of lookupcolumns etc..., but this complexity is much easier to take care of in T-SQL.A program could be written that can make adequate guesses given a source schema + destination schema,and generate some things for You, but DTS will not do that.rockmoose |
|
|
|
|
|
|
|
|
Database Schema and DTS