I have two tables , and I have to insert the values of these two tables in third table? Using cursor?

Question

1.22/5 (2 votes)

See more:

, +

Table 1             Table 2                          Table 3
Col 1     Col 2          Col 1                        Col 1      Col2
Id       Name            Doc No                       Doc No     Name
01       abc              01                           01        abc
02       def              02                           01        def

In the third tables I need the above output using CURSOR?Can any one guide me.

Posted 7-Aug-16 21:49pm

Member 12605293

Updated 9-Aug-16 5:01am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 8-Aug-16 3:58am

Do you really wany to insert data or do you want to dispay it?

Member 12605293 9-Aug-16 4:12am

Insert Data dynamically if we add the name in future.

2 solutions

Solution 1

Hi,

Please follow this link for cursor example for update
cursor example for update

Detailed exaplanation about cursor and with examples how it works.
cursor Information

Thanks
:)

Posted 7-Aug-16 22:05pm

vani suresh

Comments

Member 12605293 9-Aug-16 8:21am

Just imagine it is a School ... where in TABLE1 : ID and Name (Column names) TABLE2: DepartmentCode (Column name) my Output should be in TABLE3 where all IDs and Name Should come for each Doc No...Example In a school they are having departmentcode for (ENG-001,MATH-002,SOCIAL-003,GENSCIENCE-004,GEOGRAPHY-005) ,If we take a particular Student ID and Name from Table 1 he has to study all the subjects so when we choose (Social-003 all the IDs and Name should be in Social) like wise every candidate has to come under all the departments.This shouls come in third table

vani suresh 9-Aug-16 10:06am

Hi ,

The output should be like this ?
Id Name Department code
1 vani ENG-001
2 vani MATH-002
3 vani SOCIAL-001
4 vani GENSCIENCE-002
5 vani GEOGRAPHY-001
6 Ravi ENG-001
Let me know , If i miss any columns in third table

Member 12605293 10-Aug-16 1:15am

Hi,
Thanks for your quick reply.In third table i don't want ID only Name and Dept Code...And I don't want to use JOIN ,in the example query which you gave has JOIN.If a new candidate has joined means it must be added dynamically.

vani suresh 10-Aug-16 1:23am

Hi,

Please check the query i am only fetching name and department code and inserting into third table,
Inside the third table it have it's own ID (auto increment).But it's up to you Third table design.

vani suresh 10-Aug-16 1:51am

Hi,

Please check the query ,updated as per your comments.

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)

vani suresh · Accepted Answer · 2016-08-09T05:01:00

Solution 2

Hi,

SQL

SET NOCOUNT ON
DECLARE @name varchar(50)
DECLARE @DepartmentCode varchar(50)

-- Declare the cursor using the declare keyword
Declare StudentIDCursor CURSOR FOR 
SELECT name,DeaprtmentCode
FROM Student,[Subject]

-- Open statement, executes the SELECT statment
-- and populates the result set
Open StudentIDCursor

-- Fetch the row from the result set into the variable
Fetch Next from StudentIDCursor into @name,@DepartmentCode

-- If the result set still has rows, @@FETCH_STATUS will be ZERO
While(@@FETCH_STATUS = 0)
Begin
-- PRINT Name : '+@name+ ', DepartmentCode : '+convert(varchar(20),@DepartmentCode)'
 
INSERT INTO [Class]([Name],[DepartmentCode])
     VALUES
           (@name,@DepartmentCode)

 
 Fetch Next from StudentIDCursor into @name,@DepartmentCode
End
CLOSE StudentIDCursor 

DEALLOCATE StudentIDCursor
SET NOCOUNT OFF

Here is the o/p
====================

C#

Name    DepartmentCode
vani    ENG-001
vani    MATH-002
vani    SOCIAL-003
vani    GENSCIENCE-004
vani    GEOGRAPHY-005
    soni    ENG-001
    soni    MATH-002
    soni    SOCIAL-003
    soni    GENSCIENCE-004
    soni    GEOGRAPHY-005
    ravi    ENG-001
    ravi    MATH-002
    ravi    SOCIAL-003
    ravi    GENSCIENCE-004
    ravi    GEOGRAPHY-005

Posted 9-Aug-16 5:01am

vani suresh

Updated 9-Aug-16 19:50pm

v4

Comments

Member 12605293 12-Aug-16 2:46am

I Have tried but is not working.
The above was the example scenario given by me.
Here is my original query.

I have three tables 1.ORSC 2.@EA_OFUM 3.@EA_FUM1
From table one and table two and table three I have to pic datas and Store in Table three and update it.

Declare Fuel CURSOR FOR
SELECT Document Posting Date,Project Code,Project Name,GroupName,Machine Id,Machine Name,Issued
FROM @EAMIN_OFUM,[ORSC],[@EAMIN-FUM1]
OPEN fuel
FETCH Next From Fuel into @Document Posting Date,@Project Code,@Project Name,@GroupName,MachineId,@Machine Name,@Issued
while (@@FETCH_STATUS=0)
INSERT INTO [@EAMIN_FUM1](Document Posting Date,Project Code,Project Name,Equipment Type,Machine Id,Machine Name,Today Issued Qty)
VALUES (@doc posting date,@project code,@project name,@Equipment type,@machine Id,@Machine Name,@Today issued Qty)
FETCH Next From Fuel into @Document Posting Date,@Project Code,@Project Name,@GroupName,MachineId,@Machine Name,@Issued
End
CLOSE Fuel
DEALLOCATE Fuel
SET NOCOUNT OFF

Scalar Error Showing

vani suresh 12-Aug-16 4:37am

You already, Downvoted my answer and whatever i did as per your comments and questions,
And again your query is doesn't work means then it's not my problem.

But anyhow, Have you put the SET NOCOUNT ON
And Declare variables for all fields?

Because,Those information was missing in your query.

And why the declare variables are different in FETCH Line and values.
Always will be the same , There is no @Equipment type in Fetch line.

Please correct all your query.

Member 12605293 12-Aug-16 13:23pm

Hi Vani
Thanks for your Quick reply.I apologize for my mistakes.But when I execute it shows Document Posting Date(Incorrect syntax near Date comes) and what is meant by Scalar variable?

Member 12605293 13-Aug-16 4:53am

Hi Vani
Can I contact you via Email and share my folder to you need to get the result as per that document

Member 12605293 13-Aug-16 7:22am

Hi Vani
Can you Please Help me in this by sharing Your mail ID so that I can send my Excel Sheet

Member 12605293 14-Aug-16 2:35am

Hi Vani
Thanks for sharing the details .Check Your Mail and let me know if you have'nt received I will send it again.