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

13 May 2005 2:41 AM

Souris

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,

13 May 2005 3:09 AM

--CELKO--

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

13 May 2005 3:36 AM

Souris

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,

Show quote

"--CELKO--" wrote:

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

13 May 2005 3:50 AM

Chandra

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.developersdex.com/gurus/default.asp?p=4223
---------------------------------------

Show quote

"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:
>
> > >> 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.
> >
> >

13 May 2005 3:57 AM

Souris

Chandra,

Thanks for the message,

Does you code suport composite keys?
I have 3 primary key fields.

Your help is great appreciated,

Show quote

"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.developersdex.com/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:
> >
> > > >> 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.
> > >
> > >

13 May 2005 4:10 AM

Chandra

No! the code does not support composite keys

--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.developersdex.com/gurus/default.asp?p=4223
---------------------------------------

Show quote

"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 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.developersdex.com/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:
> > >
> > > > >> 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.
> > > >
> > > >

13 May 2005 8:21 PM

--CELKO--

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

--

13 May 2005 8:17 PM

--CELKO--

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

13 May 2005 11:36 PM

Souris

I use Access to download data into local table.
Inject data into SQL server from Access table throuhg ODBC connection.
What's kind the problem I will have.
It seems that the data may transfer correctly.

Show quote

"--CELKO--" wrote:

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

13 May 2005 5:04 AM

Sarav

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

Show quote

"Souris" <Sou***@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,
>
>

13 May 2005 7:08 PM

Souris

Thanks for the inforamtion,

The insert part working
The delete aprt seems does not work.
can you help me on delete part?

Show quote

"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" <Sou***@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,
> >
> >
>
>
>