read record one-by-one on some condition from table and insert record into another table

Question

0.00/5 (No votes)

See more:

hi
every one,

i have table its name is tblRead (source table) and this table contain 5 column (date,customer,employeeNo,item,qty,flag) and destination table is tblwrite with same structure.

i want to read record from source table and check same time this record is available in destination table or not on base of 4 column (date,customer,item,flag)validation, if record is already in destination table then record will be not insert else record insert and flag will be update in source table 0 to 1.

please help me

thanks

Posted 14-Jul-12 23:55pm

vaquas

Add a Solution

Comments

__TR__ 15-Jul-12 6:23am

If I understand correctly you want to retrieve all records from table tblRead and insert it to tblwrite if the date,customer,item,flag columns dont have the same value in both the tables. Is that right?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

abhinavvijay · Accepted Answer · 2012-07-15T01:06:00

you can use cursor for the above said problem

SQL

Declare @date DateTime,@customer varchar(5),@employeeNo int,@item int,@qty int,@flag varchar(50)
DECLARE @cursor CURSOR FOR
SELECT date,customer,employeeNo,item,qty,flag FROM tblRead

OPEN @cursor

FETCH NEXT FROM @cursor
INTO @date,@customer,@employeeNo,@item,@qty,@flag

WHILE @@FETCH_STATUS = 0
BEGIN
     if((select count(*) from tblwrite where date=@date,customer=@customer,item=@item,flag=@flag)=0)
     begin
          insert into tblWrite(date,customer,employeeNo,item,qty,flag)
          value(@date,@customer,@employeeNo,@item,@qty,@flag)
     end
end

Abdul Quader Mamun · Accepted Answer · 2012-07-15T00:39:00

Solution 1

SQL

MERGE tblwrite AS w
USING (SELECT * FROM tblRead ) AS r
ON w.employeeNo= r.employeeNo
WHEN  MATCHED THEN
--update statement for source table
WHEN NOT MATCHED THEN
--insert statement for destination table

--------

SQL

declare @date datetime
declare @route datetime
declare @employeeNo int


MERGE tblwrite AS w
USING (SELECT * FROM tblRead ) AS r
ON w.employeeNo= r.employeeNo
WHEN  MATCHED THEN
IF EXISTS(SELECT * FROM emplyeetransaction WHERE date=@date and route=#route and employeeno=@employeeno)
--update statement for source table

WHEN NOT MATCHED THEN
--insert statement for destination table

Thanks,
Mamun

Posted 15-Jul-12 0:39am

Abdul Quader Mamun

Updated 16-Jul-12 17:59pm

v5

Comments

Abdul Quader Mamun 15-Jul-12 6:41am

was that helpful! don't be late to vote me.

vaquas 16-Jul-12 4:02am

how check the condition before update statement

i want check one condition like
select employeetype from emplyeetransaction where date=@date and routes in (@routes) and employeetype =1

when this condition is true then update statement execute

thanks

Abdul Quader Mamun 16-Jul-12 23:55pm

I have update the solution for you. put everything in a procedure. and tr with this

__TR__ · Accepted Answer · 2012-07-15T03:45:00

Here is another approach

SQL

BEGIN TRY
  BEGIN TRANSACTION

    INSERT INTO tblwrite
    (
      date,
      Customer,
      EmployeeNo,
      Item,
      qty,
      flag
    )
    SELECT R.date, R.Customer, R.EmployeeNo, R.Item, R.qty, R.flag
    FROM tblRead R
    LEFT JOIN tblWrite W ON R.date = W.date AND R.Customer = W.Customer AND R.Item = W.Item AND R.Flag = W.Flag
    WHERE W.EmployeeNo IS NULL AND R.Flag = 0

    --Update the flag in source table after insert
    UPDATE tblRead
    SET Flag = 1
    WHERE Flag = 0

  COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
  ROLLBACK TRANSACTION
 END CATCH

read record one-by-one on some condition from table and insert record into another table

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0