Need Help on SQL Insert Query

Question

Need Help on SQL Insert Query

ManiDBLover

SSC Veteran

Points: 236
More actions
August 15, 2015 at 11:45 am

#299775

Hi Guys,
I have a requirement to Insert Column 1 and Column 2 based on below condition only. Looking for a Store procedure or query
Condition : Allow Insert when column 1 and Column 2 have same values on 2nd row insert. But should not allow insert when Column 2 value is different.
ALLOW INSERT:
Column1 Column2
A0007 12-Aug
A0007 12-Aug
A0007 12-Aug
DONOT ALLOW INSERT: (COLUMN1 ID should not allow different dates)
Column1 Column2
A0007 23-Mar
A0007 02-Feb
FINAL OUTPUT Should be
Column1Column2
A000712-Aug
A000712-Aug
A000712-Aug
B000220-Jun
B000220-Jun
C000330-Sep
Discard Insert when Column1 ID's comes with Different dates.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182889 More actions · Answer 1

ManiDBLover (8/15/2015)
Hi Guys,
I have a requirement to Insert Column 1 and Column 2 based on below condition only. Looking for a Store procedure or query
Condition : Allow Insert when column 1 and Column 2 have same values on 2nd row insert. But should not allow insert when Column 2 value is different.
ALLOW INSERT:
Column1 Column2
A0007 12-Aug
A0007 12-Aug
A0007 12-Aug
DONOT ALLOW INSERT: (COLUMN1 ID should not allow different dates)
Column1 Column2
A0007 23-Mar
A0007 02-Feb
FINAL OUTPUT Should be
Column1Column2
A000712-Aug
A000712-Aug
A000712-Aug
B000220-Jun
B000220-Jun
C000330-Sep
Discard Insert when Column1 ID's comes with Different dates.

Quick question, can you give us some more information, i.e. what determines the order of the rows, where is the data coming from and what are you trying to insert it into?

😎

ManiDBLover SSC Veteran Points: 236 More actions · Answer 2

Quick question, can you give us some more information, i.e. what determines the order of the rows, where is the data coming from and what are you trying to insert it into?

Data is coming from Application UI. Application has the input fields Emp ID and Transaction Date

Application Grid should show the inserted records as below.

SNO Emp_ID Transaction_Date

1 A00021 20-AUG

2 A00021 20-AUG

3 A00021 20-AUG

4 B00030 21-MAY

5 B00030 21-MAY

6 C00012 31-JUN

Please refer the attachment for Application Inputs and Database table values.

Note - An Employee ID can make any number of transactions on Same dates. that should be captured like above. Employee ID with Different transaction date is not allowed to be inserted.

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 3

Understood that the data is coming from an application. How are you passing the data to the database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ManiDBLover SSC Veteran Points: 236 More actions · Answer 4

ManiDBLover

SSC Veteran

Points: 236

August 16, 2015 at 8:35 pm

#1820350

Using inline insert query. Not a stored procedure