MS SQL Server - Updating one table with data from another table

Author

Updating one table with data from another table

STAARTECH

2006-02-17, 7:23 am

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 SQL
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?

Help is very much appreciated.
Alex

Jack Vamvas

2006-02-17, 7:23 am

Just so I can be clear, in the B) part of the question , do you just want to
add the data to the Perm table ?
is one of the columns the PK?
or do you want a situation where it will UPDATE if the PK is the same and do
an INSERT if PK is new?

--
Jack Vamvas
____________________
____________________
____________________
______
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"STAARTECH" <aglehming@hotmail.com> wrote in message
news:1140122337.843205.71770@g44g2000cwa.googlegroups.com...
> 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 SQL
> 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?
>
> Help is very much appreciated.
> Alex
>

David Portas

2006-02-17, 7:23 am

Answered in .programming

Please don't multi-post.

--
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
--

Dutt

2006-02-17, 7:23 am

Hi
I'm giving you required solution.........
UPDATE permtable
SET ColumnA=
(SELECT Column1 from
FROM temptable
WHERE temptable. Column1= permtable.ColumnA)

similarly........other columns
bye

Sponsored Links