move records from table1 to table2 if does not exist in table3

I want to move records from table1 to table2 if the records in table1 does
not exist in table3
I have following code:
START TRANSACTION
INSERT INTO TABLE2
SELECT * FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE1, TABLE3
WHERE TABLE1.ID = TABLE3.ID)
DELETE FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE1, TABLE3
WHERE TABLE1.ID = TABLE3.ID)
IF @.@.ERROR <> 0 THEN
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
should above code work?
Are there any difference to change insert clause like following code?
INSERT INTO TABLE2
SELECT * FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE2, TABLE3
WHERE TABLE2.ID = TABLE3.ID)
Any information is great appreciated,>> I want to move records [sic] from table1 to table2 if the records
[sic] in table1 does
not exist in table3 <<
First of all, get a book and learn the basics. Records and rows are
totally different concepts. Next, in SQL we do not move things around
like you did with punch card and magnetic tape file sytems in the
1950's.
Tables are not files. A table is the ONLY model of set of the same
kind of things in a schema. Having tables with the same structure is
totally wrong; even Chris date agrees with me on this one! It is that
bad an error.
You need a status code or a rule that classifies your entities into
these magical, undefined 1,2, and 3 categories.|||Thanks for your message.
Are you saying that the tables do not normalize?
I need download a database from a mainframe to my SQL server and give user
information.
Because of security issue, I need store on MS Access database locally and
move to SQL server.
I need move records around.
Database design should follow the business and security rules to resolve
business problems.
It happens in the real world normalization and demalization.
If I am wrong please let me know.
If you could can you please help me on the SQL statements
Thanks again,
"--CELKO--" wrote:

> [sic] in table1 does
> not exist in table3 <<
> First of all, get a book and learn the basics. Records and rows are
> totally different concepts. Next, in SQL we do not move things around
> like you did with punch card and magnetic tape file sytems in the
> 1950's.
> Tables are not files. A table is the ONLY model of set of the same
> kind of things in a schema. Having tables with the same structure is
> totally wrong; even Chris date agrees with me on this one! It is that
> bad an error.
> You need a status code or a rule that classifies your entities into
> these magical, undefined 1,2, and 3 categories.
>|||Hi Souris,
How about this one:
INSERT INTO TABLE2
SELECT * FROM TABLE1 WHERE TABLE1.ID NOT IN (SELECT TABLE3.ID FROM TABLE3)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"Souris" wrote:
> Thanks for your message.
> Are you saying that the tables do not normalize?
> I need download a database from a mainframe to my SQL server and give user
> information.
> Because of security issue, I need store on MS Access database locally and
> move to SQL server.
> I need move records around.
> Database design should follow the business and security rules to resolve
> business problems.
> It happens in the real world normalization and demalization.
> If I am wrong please let me know.
> If you could can you please help me on the SQL statements
> Thanks again,
>
>
> "--CELKO--" wrote:
>|||Chandra,
Thanks for the message,
Does you code suport composite keys?
I have 3 primary key fields.
Your help is great appreciated,
"Chandra" wrote:
> Hi Souris,
> How about this one:
> INSERT INTO TABLE2
> SELECT * FROM TABLE1 WHERE TABLE1.ID NOT IN (SELECT TABLE3.ID FROM TABLE3)
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.examnotes.net/gurus/default.asp?p=4223
> ---
>
> "Souris" wrote:
>|||No! the code does not support composite keys
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"Souris" wrote:
> Chandra,
> Thanks for the message,
> Does you code suport composite keys?
> I have 3 primary key fields.
> Your help is great appreciated,
>
> "Chandra" wrote:
>|||Hi,
Insert into Table2
Select t1.* From Table1 t1 Left Outer Join Table3 t3 On t1.ID = t3.ID
Where t3.ID is NULL
The above query will perform better and it will also support composite key,
for composite key just add the additional condition on the left outer join
like this:
Insert into Table2
Select t1.* From Table1 t1 Left Outer Join Table3 t3 On t1.ID = t3.ID And
t1.field2 = t3.field2
Where t3.ID is NULL
regards,
Sarav...
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:0039DB89-389A-4CAA-B9F7-A828B3CFE9B6@.microsoft.com...
>I want to move records from table1 to table2 if the records in table1 does
> not exist in table3
> I have following code:
> START TRANSACTION
> INSERT INTO TABLE2
> SELECT * FROM TABLE1
> WHERE NOT EXISTS(SELECT * FROM TABLE1, TABLE3
> WHERE TABLE1.ID = TABLE3.ID)
> DELETE FROM TABLE1
> WHERE NOT EXISTS(SELECT * FROM TABLE1, TABLE3
> WHERE TABLE1.ID = TABLE3.ID)
> IF @.@.ERROR <> 0 THEN
> ROLLBACK TRANSACTION
> ELSE
> COMMIT TRANSACTION
>
> should above code work?
>
> Are there any difference to change insert clause like following code?
> INSERT INTO TABLE2
> SELECT * FROM TABLE1
> WHERE NOT EXISTS(SELECT * FROM TABLE2, TABLE3
> WHERE TABLE2.ID = TABLE3.ID)
>
> Any information is great appreciated,
>|||Thanks for the inforamtion,
The insert part working
The delete aprt seems does not work.
can you help me on delete part?
"Sarav" wrote:

> Hi,
> Insert into Table2
> Select t1.* From Table1 t1 Left Outer Join Table3 t3 On t1.ID = t3.ID
> Where t3.ID is NULL
> The above query will perform better and it will also support composite key
,
> for composite key just add the additional condition on the left outer join
> like this:
> Insert into Table2
> Select t1.* From Table1 t1 Left Outer Join Table3 t3 On t1.ID = t3.ID And
> t1.field2 = t3.field2
> Where t3.ID is NULL
> regards,
> Sarav...
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:0039DB89-389A-4CAA-B9F7-A828B3CFE9B6@.microsoft.com...
>
>|||>> I need download a database from a mainframe to my SQL server and
give user information. <<
What ETL tools are you using? SQL and ACCESS are different and you
will have problems if you try to put ACCESS betrween the mainframe and
the SQL Server.|||>> Does you code suport composite keys? I have 3 primary key fields
[sic] <<.
INSERT INTO Table2
SELECT *
FROM Table1 AS T1
WHERE NOT EXISTS
(SELECT *
FROM Table3 AS T3
WHERE T3.key_1 = T1.key_1
AND T3.key_2 = T1.key_2
AND T3.key_3 = T1.key_3 );