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
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.
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)
--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
Thanks ITTRUCKER