|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Updating Table with data from another tableI am a newbie at SQL, and I have a two-part question, that I am hoping you can help me with. Please bear with me. Original Issue: We have a non-MS-SQL database that exports data into many text files on a daily basis. We then use DTS jobs to import(append) the text files into MS SQL2000 tables that we use for reporting. Problem is that every once in a while, the other db exports data into the text files that matches existing primary keys in our SQL Tables. This causes the DTS to fail because of a primary key violation and the data for that day does not get added into our SQL table. This of course causes our reporting to go haywire. Attempted Solution: Based on my research, I have come up with the following solution: 1) Create a TempTable in SQL 2) Import the data from the text file into the TempTable 3) Use an Update SQL task to update the data in PermTable with the data in TempTable, without causing PK violation errors or duplicating entries. (In case of Primary Keys matching, I do not care if it overwrites the existing data in the PermTable or if it just keeps the existing data and moves on to the next row) 4) Drop the TempTable in SQL. I figured out how to do 1, 2 & 4 but I have not found any good examples for an Update SQL statement that I could adapt. My question is as follows: A) Am I on the right track or is there an easier way to solve the original issue. B) If I am on the right track, then I would appreciate it if somebody could write a sql UPDATE statement for me using the following variables if at all possible. TempTable PermTable Column1 = ColumnA Column2 = ColumnB Column3 = ColumnC Also, to satisfy my curiosity, does an Update statement overwrite data or just ignore the existing row and move on to the next row? I would assume overwrite. I know, I need to read the books, but I just got SQL dumped in my lap. Any suggestions on which books to start with? Help is very much appreciated. StaarTech StaarTech wrote:
> In In that case why not just insert the rows that don't already exist:> case of Primary Keys matching, I do not care if it overwrites the existing > data in the PermTable or if it just keeps the existing data and moves on to > the next row) INSERT INTO target_table (key_col, col1, col2, ...) SELECT key_col, col1, col2, ... FROM source_table AS S WHERE NOT EXISTS (SELECT * FROM target_table AS T WHERE T.key_col = S.key_col); -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- You are off to a good start with the idea of using a temporary import table.
To insert rows that will not cause a primary key violation: INSERT INTO PermTable SELECT Column1, Column2, Column3 FROM TempTable t1 WHERE NOT EXISTS (SELECT 1 FROM PermTable t2 WHERE t1.Column1 = t2.ColumnA ) I made the assumption here that ColumnA is the primary key column for PermTable. If it is not, you will have to adjust the columns compared in the statement above. To update rows in PermTable that have a matching row in TempTable UPDATE PermTable Set ColumnB = t1.Column2, ColumnC = t1.Column3 FROM TempTable t1 INNER JOIN PermTable t2 ON t2.ColumnA = t1.Column1 Again, I made the assumption that ColumnA is the primary key column of PermTable, and Column1 is the PK column of the temporary import table TempTable. The only thing that you can get into trouble with here is if your applications allow changes to the primary key columns. -- Show quote"StaarTech" wrote: > Hi All: > > I am a newbie at SQL, and I have a two-part question, that I am hoping you > can help me with. Please bear with me. > > Original Issue: > We have a non-MS-SQL database that exports data into many text files on a > daily basis. > We then use DTS jobs to import(append) the text files into MS SQL2000 tables > that we use for reporting. > Problem is that every once in a while, the other db exports data into the > text files that matches existing primary keys in our SQL Tables. This causes > the DTS to fail because of a primary key violation and the data for that day > does not get added into our SQL table. This of course causes our reporting > to go haywire. > > Attempted Solution: > Based on my research, I have come up with the following solution: > 1) Create a TempTable in SQL > 2) Import the data from the text file into the TempTable > 3) Use an Update SQL task to update the data in PermTable with the data in > TempTable, without causing PK violation errors or duplicating entries. (In > case of Primary Keys matching, I do not care if it overwrites the existing > data in the PermTable or if it just keeps the existing data and moves on to > the next row) > 4) Drop the TempTable in SQL. > > I figured out how to do 1, 2 & 4 but I have not found any good examples for > an Update SQL statement that I could adapt. > > My question is as follows: > A) Am I on the right track or is there an easier way to solve the original > issue. > B) If I am on the right track, then I would appreciate it if somebody could > write a sql UPDATE statement for me using the following variables if at all > possible. > TempTable PermTable > Column1 = ColumnA > Column2 = ColumnB > Column3 = ColumnC > Also, to satisfy my curiosity, does an Update statement overwrite data or > just ignore the existing row and move on to the next row? I would assume > overwrite. > > I know, I need to read the books, but I just got SQL dumped in my lap. Any > suggestions on which books to start with? > Help is very much appreciated. > StaarTech I have just two small points to add to Mark's excellent advice.
First, if the data coming in is a regular production process I use a permanent table, not a temporary one. It is easy enough to write the process to clear the table of the rows once they have been applied to production. Second, put the UPDATE of existing rows before the INSERT of missing rows. If the INSERT comes first you will be updating the up to date rows you just inserted! Roy I had a nagging feeling about which statement should come first! Thanks.
-Mark Williams -- Show quote"Roy Harvey" wrote: > I have just two small points to add to Mark's excellent advice. > > First, if the data coming in is a regular production process I use a > permanent table, not a temporary one. It is easy enough to write the > process to clear the table of the rows once they have been applied to > production. > > Second, put the UPDATE of existing rows before the INSERT of missing > rows. If the INSERT comes first you will be updating the up to date > rows you just inserted! > > Roy > Any suggestions as to which SQL books to start reading?
StaarTech Show quote "Mark Williams" wrote: > I had a nagging feeling about which statement should come first! Thanks. > > -Mark Williams > > -- > "Roy Harvey" wrote: > > > I have just two small points to add to Mark's excellent advice. > > > > First, if the data coming in is a regular production process I use a > > permanent table, not a temporary one. It is easy enough to write the > > process to clear the table of the rows once they have been applied to > > production. > > > > Second, put the UPDATE of existing rows before the INSERT of missing > > rows. If the INSERT comes first you will be updating the up to date > > rows you just inserted! > > > > Roy > > As a newbie who also had SQL dumped in my lap I found Murachs SQL for
SQL Server indespensible - it was an older book that I actaully checked out of the library but it's examples were all independently relevant and very well organized so it acts as a look-up / reference manual. http://www.murach.com/books/sqls/index.htm I just started reading Beginning Transact-SQL with SQL 2000 & 2005 (Wrox by Turley with Wood). It offers more complex topics and more of a real-world best practices approach but the chapters are sequential, though I am learning a lot it's not very useful as a reference. http://www.wrox.com/WileyCDA/WroxTitle/productCd-076457955X.html ~mahalie StaarTech wrote: Show quote > Any suggestions as to which SQL books to start reading? > > > StaarTech > > > "Mark Williams" wrote: > > > I had a nagging feeling about which statement should come first! Thanks. > > > > -Mark Williams > > > > -- > > "Roy Harvey" wrote: > > > > > I have just two small points to add to Mark's excellent advice. > > > > > > First, if the data coming in is a regular production process I use a > > > permanent table, not a temporary one. It is easy enough to write the > > > process to clear the table of the rows once they have been applied to > > > production. > > > > > > Second, put the UPDATE of existing rows before the INSERT of missing > > > rows. If the INSERT comes first you will be updating the up to date > > > rows you just inserted! > > > > > > Roy > > >  |
|||||||||||||||||||||||
Other interesting topics