I have a problem trying to work out the insert logic for an insert statement
which inserts the rows from one table (Parcel1) into another table (Parcel2)
.
Bare with me for this as I know this mightn't people might say why are you
doing this but its just an example i've drew up. Basically my problem is
that the first time I run an insert statement everything works fine. When I
run the insert statement the second time though the same rows are inserted
into the second table.
What i want to add to my insert statement is something which says if the
fields oneParcel1 and oneParcel2 for a particular row have the same values i
n
the twoparcel1 and twoparcel2 fields as a row which already exists in the
Parcel2 table then don't insert the records. So If a row in Parcel1 has a
row which has car and truck in the table parcel2 regardless of the other
fields value don't insert it.
CREATE TABLE [dbo].[Parcel1] (
[RecNo] [int] NULL ,
[oneParcel1] [bigint] NULL ,
[oneParcel2] [bigint] NULL ,
[Date] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Parcel2] (
[RecNO] [bigint] NULL ,
[twoParcel1] [bigint] NULL ,
[twoParcel2] [bigint] NULL ,
[Date] [datetime] NULL
) ON [PRIMARY]
insert into Parcel1 values(1, 'car', 'bus', '15/06/1982')
insert into Parcel1 values(2, 'bus', 'train', '15/06/1982')
insert into Parcel1 values(3, 'truck', 'car', '15/06/1982')
insert into Parcel1 values(4, 'car', 'truck', '15/06/1982')
insert into Parcel1 values(5, 'truck', 'plane', '15/06/1982')
Table: Parcel1
RecNo oneParcel1 oneParcel2 Date
1 car bus 15/06/1982
2 bus train 15/06/1982
3 truck car 15/06/1982
4 car truck 15/06/1982
5 truck boat 15/06/1982
After 1st insert. I want this. and if I run the insert statement again I
don't want these rows to be inserted again
Table: Parcel2
RecNo twoParcel1 twoParcel2 Date
1 car bus 15/06/1982
2 bus train 15/06/1982
3 truck car 15/06/1982
4 car truck 15/06/1982
5 truck boat 15/06/1982Stephen
Have you even checked your DDL before posting it?
CREATE TABLE [dbo].[Parcel1] (
[RecNo] [int] NULL ,
[oneParcel1] varchar(15) NULL ,
[oneParcel2] varchar(15) NULL ,
[Date] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Parcel2] (
[RecNO] [bigint] NULL ,
[twoParcel1] varchar(15) NULL ,
[twoParcel2] varchar(15) NULL ,
[Date] [datetime] NULL
) ON [PRIMARY]
insert into Parcel1 values(1, 'car', 'bus', '19820615')
insert into Parcel1 values(2, 'bus', 'train', '19820615')
insert into Parcel1 values(3, 'truck', 'car', '19820615')
insert into Parcel1 values(4, 'car', 'truck', '19820615')
insert into Parcel1 values(5, 'truck', 'plane', '19820615')
INSERT INTO Parcel2 SELECT * FROM Parcel1 WHERE NOT EXISTS
(
SELECT * FROM Parcel2 P WHERE P.RecNo=Parcel1.RecNo
)
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:E956A4AA-4D55-4056-AA86-4391054E53BE@.microsoft.com...
>I have a problem trying to work out the insert logic for an insert
>statement
> which inserts the rows from one table (Parcel1) into another table
> (Parcel2).
>
> Bare with me for this as I know this mightn't people might say why are you
> doing this but its just an example i've drew up. Basically my problem is
> that the first time I run an insert statement everything works fine. When
> I
> run the insert statement the second time though the same rows are inserted
> into the second table.
> What i want to add to my insert statement is something which says if the
> fields oneParcel1 and oneParcel2 for a particular row have the same values
> in
> the twoparcel1 and twoparcel2 fields as a row which already exists in the
> Parcel2 table then don't insert the records. So If a row in Parcel1 has a
> row which has car and truck in the table parcel2 regardless of the other
> fields value don't insert it.
> CREATE TABLE [dbo].[Parcel1] (
> [RecNo] [int] NULL ,
> [oneParcel1] [bigint] NULL ,
> [oneParcel2] [bigint] NULL ,
> [Date] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Parcel2] (
> [RecNO] [bigint] NULL ,
> [twoParcel1] [bigint] NULL ,
> [twoParcel2] [bigint] NULL ,
> [Date] [datetime] NULL
> ) ON [PRIMARY]
> insert into Parcel1 values(1, 'car', 'bus', '15/06/1982')
> insert into Parcel1 values(2, 'bus', 'train', '15/06/1982')
> insert into Parcel1 values(3, 'truck', 'car', '15/06/1982')
> insert into Parcel1 values(4, 'car', 'truck', '15/06/1982')
> insert into Parcel1 values(5, 'truck', 'plane', '15/06/1982')
> Table: Parcel1
> RecNo oneParcel1 oneParcel2 Date
> 1 car bus 15/06/1982
> 2 bus train 15/06/1982
> 3 truck car 15/06/1982
> 4 car truck 15/06/1982
> 5 truck boat 15/06/1982
> After 1st insert. I want this. and if I run the insert statement again I
> don't want these rows to be inserted again
> Table: Parcel2
> RecNo twoParcel1 twoParcel2 Date
> 1 car bus 15/06/1982
> 2 bus train 15/06/1982
> 3 truck car 15/06/1982
> 4 car truck 15/06/1982
> 5 truck boat 15/06/1982
>|||2 ways
1. Check for the existence of a duplicate before inserting
2. Keep a unique index with IGNORE DUPLICATE option set for Parcel2 table
Rakesh
"Stephen" wrote:
> I have a problem trying to work out the insert logic for an insert stateme
nt
> which inserts the rows from one table (Parcel1) into another table (Parcel
2).
>
> Bare with me for this as I know this mightn't people might say why are you
> doing this but its just an example i've drew up. Basically my problem is
> that the first time I run an insert statement everything works fine. When
I
> run the insert statement the second time though the same rows are inserted
> into the second table.
> What i want to add to my insert statement is something which says if the
> fields oneParcel1 and oneParcel2 for a particular row have the same values
in
> the twoparcel1 and twoparcel2 fields as a row which already exists in the
> Parcel2 table then don't insert the records. So If a row in Parcel1 has a
> row which has car and truck in the table parcel2 regardless of the other
> fields value don't insert it.
> CREATE TABLE [dbo].[Parcel1] (
> [RecNo] [int] NULL ,
> [oneParcel1] [bigint] NULL ,
> [oneParcel2] [bigint] NULL ,
> [Date] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Parcel2] (
> [RecNO] [bigint] NULL ,
> [twoParcel1] [bigint] NULL ,
> [twoParcel2] [bigint] NULL ,
> [Date] [datetime] NULL
> ) ON [PRIMARY]
> insert into Parcel1 values(1, 'car', 'bus', '15/06/1982')
> insert into Parcel1 values(2, 'bus', 'train', '15/06/1982')
> insert into Parcel1 values(3, 'truck', 'car', '15/06/1982')
> insert into Parcel1 values(4, 'car', 'truck', '15/06/1982')
> insert into Parcel1 values(5, 'truck', 'plane', '15/06/1982')
> Table: Parcel1
> RecNo oneParcel1 oneParcel2 Date
> 1 car bus 15/06/1982
> 2 bus train 15/06/1982
> 3 truck car 15/06/1982
> 4 car truck 15/06/1982
> 5 truck boat 15/06/1982
> After 1st insert. I want this. and if I run the insert statement again I
> don't want these rows to be inserted again
> Table: Parcel2
> RecNo twoParcel1 twoParcel2 Date
> 1 car bus 15/06/1982
> 2 bus train 15/06/1982
> 3 truck car 15/06/1982
> 4 car truck 15/06/1982
> 5 truck boat 15/06/1982
>|||Try:
INSERT INTO Parcel2 (recno, twoparcel1, twoparcel2, [date])
SELECT recno, oneparcel1, oneparcel2, [date]
FROM Parcel1 AS P1
WHERE NOT EXISTS
(SELECT *
FROM Parcel2 AS P2
WHERE P2.twoparcel1 = P1.oneparcel1
AND P2.twoparcel2 = P1.oneparcel2) ;
Some other things need more attention. First, you don't have a key in
either table! All the columns are nullable, which means both tables
lack any integrity. "Date" is a reserved word and much too vague for a
column name. "RecNo" is not a good identifier in a relational database.
Conventional wisdom has it that tables have rows, not records. Rows are
not numbered and surrogate keys are not "record numbers". BIGINT
appears to be a mistake anyway but are you really expecting more than 2
billion rows in these tables?
Thanks for including the DDL but do remember that keys, constraints and
accurate datatypes are important if you want accurate answers.
Hope this helps.
David Portas
SQL Server MVP
--|||Small addition to what i said
2 ways
1. Check for the existence of a duplicate before inserting
2. Keep a unique index on columns twoParcel1, twoParcel2 with IGNORE
DUPLICATE option set for Parcel2 table. This will let u do the insert
statements as u hv been doing and ignoring any duplicate values without
giving an error.
"Rakesh" wrote:
> 2 ways
> 1. Check for the existence of a duplicate before inserting
> 2. Keep a unique index with IGNORE DUPLICATE option set for Parcel2 table
> Rakesh
> "Stephen" wrote:
>|||> 2. Keep a unique index on columns twoParcel1, twoParcel2 with IGNORE
> DUPLICATE option set for Parcel2 table. This will let u do the insert
> statements as u hv been doing and ignoring any duplicate values without
> giving an error.
Be very, very careful with the IGNORE_DUP_KEY option. I would consider
it suitable for a staging database in special circumstances only - not
for a live database with actual users, queries and updates running on
it.
The reason is that IGNORE_DUP_KEY confounds set-based inserts because
by giving a non-deterministic result in the presence of duplicates. In
short you cannot know or control which rows(s) get inserted and which
get discarded. Of course, if all your developers are aware that this
option has been set then in principle they can safely code around it -
but if they are going to do that anyway then why use it? Just add the
existence check to the INSERT statements instead.
David Portas
SQL Server MVP
--|||Sorry never checked it correct your guess was correct to what it should have
been.
I know this is going to sound awkward but I know who to do it that way but
i'm trying to ignore the RecNO and work out how to only insert records where
Parcel1.oneParcel1 = Parcel2.twoParcel1 AND
Parcel1.oneParcel2 = Parcel2.twoParcel2
I know this probably doesn't make sense and its hard to explain but i want
the sql to not allow records to be inserted when the above conditions are
both true. In other words forgetting about the recno and date if a row in th
e
Parcel 1 table has the values
RecNo oneParcel1 oneParcel2 Date
1 car bus 15/06/1982
and the same row is present in the Parcel 2 table like this
RecNo twoParcel1 twoParcel2 Date
1 car bus 15/06/1982
When a row like this comes along in the Parcel 1 table I don't want it
inserted because the car and buss secenario has already been inserted.
RecNo oneParcel1 oneParcel2 Date
6 car bus 01/11/1983
"Uri Dimant" wrote:
> Stephen
> Have you even checked your DDL before posting it?
> CREATE TABLE [dbo].[Parcel1] (
> [RecNo] [int] NULL ,
> [oneParcel1] varchar(15) NULL ,
> [oneParcel2] varchar(15) NULL ,
> [Date] [datetime] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Parcel2] (
> [RecNO] [bigint] NULL ,
> [twoParcel1] varchar(15) NULL ,
> [twoParcel2] varchar(15) NULL ,
> [Date] [datetime] NULL
> ) ON [PRIMARY]
> insert into Parcel1 values(1, 'car', 'bus', '19820615')
> insert into Parcel1 values(2, 'bus', 'train', '19820615')
> insert into Parcel1 values(3, 'truck', 'car', '19820615')
> insert into Parcel1 values(4, 'car', 'truck', '19820615')
> insert into Parcel1 values(5, 'truck', 'plane', '19820615')
>
> INSERT INTO Parcel2 SELECT * FROM Parcel1 WHERE NOT EXISTS
> (
> SELECT * FROM Parcel2 P WHERE P.RecNo=Parcel1.RecNo
> )
>
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:E956A4AA-4D55-4056-AA86-4391054E53BE@.microsoft.com...
>
>
Can you post the full ddl for the table, plus any others that are related to
Tharnid: