March 25, 2021 at 8:51 pm
Hi All.I have a wired situation here.
I have 2 tables: table 1 with let say 5 records on it and an empty table2 (same schema), Trying to copy records from table1 into table2. When my script says(i am trying to make my script simple)
with x as
(select * from table1
)
insert into table2
some of the records are missing. Let's say records 1 and 3 are missing.
when the same script says
(select * from table1
where records in (1,3)
)
insert into table2
1 and 3 records are getting inserted
how is this possible?
March 25, 2021 at 8:54 pm
Correction:
with x as
(select * from table1
)
insert into table2
select * from x
some of the records are missing. Let's say records 1 and 3 are missing.
when the same script says
(select * from table1
where records in (1,3)
)
insert into table2
select * from x
March 25, 2021 at 9:24 pm
It really isn't clear what problem you are having - but I am assuming the issue is that you are getting duplicates. If that is the case it is because table2 does not have anything set to block duplicates. You would need a unique constraint on the table definition to prevent duplicate rows from being inserted.
The code you have provided doesn't check for the rows that already exist - so as long as there are no constraints then SQL will just insert the rows as instructed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 25, 2021 at 9:43 pm
no duplicates. With the first script, I am trying to get all 5 records, but getting only 3 of them. With the second script, I am trying to see if I can insert the missing 2 records.2nd script inserts the missing records. My question is why script 1 could not get all records?
March 26, 2021 at 7:07 am
Can you provide some code that demonstrates the problem, which we can run for ourselves?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply