Transfer from one table to another

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transfer from one table to another

Author

Topic

snipered
Starting Member

9 Posts

Posted - 2006-10-16 : 08:41:22

Hi, normally this is pretty easy but it's not working.

I have two tables. I want to transfer from one table to another where the cylinder number and line number are not the same. So the destination table will only ever have one cylinder number and line number.

The database was created by someone else. The primary key is none of the above two fields. Its an autonumber generated field.

The problem, nothing is being transfered, even though the data is different.

Any ideas?

strSQL = "INSERT INTO [tbl_Delupdate] ([Transaction Date],[Line Number],[Cylinder Number]) "
strSQL = strSQL & "SELECT [Transaction Date],[Line Number],[Cylinder Number] FROM [tbl_TransactionMaster] "
strSQL = strSQL & "WHERE [tbl_Delupdate].[Cylinder Number] <> [tbl_TransactionMaster].[Cylinder Number] "
strSQL = strSQL & "AND [tbl_Delupdate].[Line Number] <> [tbl_TransactionMaster].[Line Number] "

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 08:46:24

[code]INSERT INTO [tbl_Delupdate]
(
[Transaction Date],
[Line Number],
[Cylinder Number]
)
SELECT t1.[Transaction Date],
t1.[Line Number],
t1.[Cylinder Number]
FROM [tbl_TransactionMaster] t1
INNER JOIN [tbl_Delupdate] t2 ON t2.some_column = t1.some_column
WHERE t1.[Cylinder Number] <> t2.[Cylinder Number]
AND t1.[Line Number] <> t2.[Line Number][/code]

Peter Larsson
Helsingborg, Sweden

snipered
Starting Member

9 Posts

Posted - 2006-10-16 : 09:01:45

Hi, the only thing about the above code is that it joins the two tables via a single column. The problem is the only field that is the same for both tables is the cylinder number and line number.

I tried replacing some_column with cylinder number and it transfered 0 rows.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-16 : 09:07:04

There has to be at last one column uniquely identifying the rows for both tables. Otherwise checking for "not the same" is pointless. Maybe you just should truncate the target table and insert everything from source table?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources