SQLTeam.com | Weblogs | Forums

How to use CURSOR?

sql2008
sql2012

#1

Hi. I am a Newbie to SQL
My Question
I have two tables , and I have to insert the values of these two tables in third table? Using cursor?
Table 1 Table 2 Table 3(Needed Output)
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
02 abc
02 def


#2

Here's something that might work, but it'll depend on your actual table setup and data. It helps to format the question a little better, it's hard to tell what your tables look like or what the relationship between them is.

--These 3 declares are just setting up temp tables as an example for what your tables look like
DECLARE @Table1 TABLE (ID int, DocNo varchar(10), Name varchar(20))
DECLARE @Table2 TABLE (ID int, DocNo varchar(10), Name varchar(20))
DECLARE @Table3 TABLE (ID int, DocNo varchar(10), Name varchar(20))

--These inserts are just putting some dummy data into the first two tables to use in the example
INSERT INTO @Table1
VALUES (01,'abc','def'),(02, 'def','ghi')

INSERT INTO @Table2
VALUES (03,'ghi','jkl'),(04, 'jkl','mno')

--These selects are just so you can see what the temp tables look like
SELECT * FROM @Table1
SELECT * FROM @Table2
SELECT * FROM @Table3

--This insert takes all the data from Table1, and all the data from Table 2 and inserts it into Table3
INSERT INTO @Table3
SELECT T1.ID, T1.DocNo, T1.Name FROM @Table1 T1
UNION ALL
SELECT T2.ID, T2.DocNo, T2.Name FROM @Table2 T2

--This select just shows you what Table3 would look like after the insert
SELECT * FROM @Table3

This probably won't work for your data structure, but I can't tell what columns are in Table 1 or 2 or what data goes into which one. Putting the DECLARE TABLE and INSERTS into the question can help a lot.


#3

My advice would be to avoid cursors at all costs, and use a set-based approach instead.

The layout of your tables / columns is not clear to me - looks like the formatting of this forum has "eaten" some of your data. You can put (e.g. re-edit your original, or re-post it) the following around your example:

    ```text
        your example data here
    ```

(Note they are back-ticks, not single-quotes)


#4


#5

--These 3 declares are just setting up temp tables as an example for what your tables look like
DECLARE @Table1 TABLE (EmpCode varchar(10), Name varchar(20))
DECLARE @Table2 TABLE (DocNo varchar(10))
DECLARE @Table3 TABLE (DocNo varchar(10), Name varchar(20))

--These inserts are just putting some dummy data into the first two tables to use in the example
INSERT INTO @Table1
VALUES ('01','Alston'),('02', 'Atilla'),('03','Joseph')

INSERT INTO @Table2
VALUES ('04'),('05'),('06')

--These selects are just so you can see what the temp tables look like
SELECT * FROM @Table1
SELECT * FROM @Table2
SELECT * FROM @Table3

--This insert takes all the data from Table1, and joins it to all the data from Table 2 for a single DocNo and inserts it into Table3
INSERT INTO @Table3
SELECT T2.DocNo, T1.Name
FROM @Table1 T1, @Table2 T2
WHERE T2.DocNo = '04'

--This select just shows you what Table3 would look like after the insert
SELECT * FROM @Table3

There's no JOIN specified in the SELECT, and there is no restriction on the join so it does a "Cartesian" join where it connects every record in Table1 with every record in Table2


#6

Thanks ITTRUCKER