Inserting a data from one table to another

Question

4.00/5 (1 vote)

See more:

Hi,

I have an senario where i want to insert for one sourcecodeid
for exm:
First table
SourceCodeId
1905
1910
1916
1918
1919

Second table

SiteId
56
101
105
106
119
141

the Result should be
1905 56
1905 101
1905 105
1905 106
1905 119
1905 141

like this i want the result plz help me.

Posted 25-Sep-12 21:28pm

chetankumar1333

Add a Solution

Comments

Pro Idiot 26-Sep-12 3:34am

There should be a relation between the 2 table , Foreign Key is the most suitable 1,
and then you can put a condition on both tables, fetch your required record , then you can string concatenate both and insert to your final table.

Oshtri Deka 26-Sep-12 3:35am

1. Give as tables' schema (i.e. columns).
2. Do you want to insert data (update records in one table with data from another) or you want result-set which combines data from two tables?

3 solutions

Solution 3

As you said i have created 2 tables

1. First Table "SourceCode_Tbl" which contains SourceCodeID column with values
1905
1910
1916
1918
1919

2. Second Table "Site_tbl" which contains SiteID column with values
56
101
105
106
119
141

Now the query to select
1905 56
1905 101
1905 105
1905 106
1905 119
1905 141
would be,

SQL

(select Str(SourceCodeID,4,4) + Str(SiteID,3,3) as RESULT from SourceCode_Tbl y
join
Site_tbl s
on y.SourceCodeID <> s.SiteID where y.SourceCodeID=1905)

OR

SQL

select Str(SourceCodeID,4,4) + Str(SiteID,3,3) as RESULT 
from SourceCodeID_Tbl, Site_tbl
where SourceCodeID = 1905
order by SourceCodeID

Posted 25-Sep-12 23:15pm

satz_770

Updated 13-Nov-12 21:39pm

v6

Solution 1

As Deepak Jena wrote: "There should be a relation between the 2 tables"!

Create another table containing ID's from both tables, for example (i use temporary tables):

SQL

CREATE TABLE #SrcCode ([SrcCodeID] INT, [SrcCodeDescription] NVARCHAR(100))
INSERT INTO #SrcCode ([SrcCodeID], [SrcCodeDescription])
	VALUES(1905, 'A')
INSERT INTO #SrcCode ([SrcCodeID], [SrcCodeDescription])
	VALUES(1910, 'B')
INSERT INTO #SrcCode ([SrcCodeID], [SrcCodeDescription])
	VALUES(1916, 'C')
INSERT INTO #SrcCode ([SrcCodeID], [SrcCodeDescription])
	VALUES(1918, 'D')
INSERT INTO #SrcCode ([SrcCodeID], [SrcCodeDescription])
	VALUES(1919, 'E')
 
CREATE TABLE #Site ([SiteID] INT, [SiteDescription] NVARCHAR(100)) 
INSERT INTO #Site ([SiteID], [SiteDescription]) 
	VALUES(56, 'ZXC')
INSERT INTO #Site ([SiteID], [SiteDescription]) 
	VALUES(101, 'XCV')
INSERT INTO #Site ([SiteID], [SiteDescription]) 
	VALUES(105, 'CVB')
INSERT INTO #Site ([SiteID], [SiteDescription]) 
	VALUES(106, 'VBN')
INSERT INTO #Site ([SiteID], [SiteDescription]) 
	VALUES(119, 'BNM')
INSERT INTO #Site ([SiteID], [SiteDescription]) 
	VALUES(141, 'NMA')
 
CREATE TABLE #SrcCodeSite (SrcCodeID INT, SiteID INT)
INSERT INTO #SrcCodeSite (SrcCodeID , SiteID )
VALUES(1905, 56)
INSERT INTO #SrcCodeSite (SrcCodeID , SiteID )
VALUES(1905, 101)
INSERT INTO #SrcCodeSite (SrcCodeID , SiteID )
VALUES(1905, 105)
INSERT INTO #SrcCodeSite (SrcCodeID , SiteID )
VALUES(1905, 106)
INSERT INTO #SrcCodeSite (SrcCodeID , SiteID )
VALUES(1905, 119)
INSERT INTO #SrcCodeSite (SrcCodeID , SiteID )
VALUES(1905, 141)

SELECT *
FROM #SrcCodeSite

SELECT SC.SrcCodeID, SC.SrcCodeDescription, SI.SiteID, SI.SiteDescription
FROM #SrcCodeSite AS SCS
	LEFT JOIN #SrcCode AS SC ON SCS.SrcCodeID = SC.SrcCodeID
	LEFT JOIN #Site AS SI ON SCS.SiteID = SI.SiteID

DROP TABLE #SrcCode
DROP TABLE #Site
DROP TABLE #SrcCodeSite

Posted 25-Sep-12 21:58pm

Maciej Los

Comments

chetankumar1333 26-Sep-12 4:36am

Hi,
Thanks for reply,it will helpfull only when we have few records,in case of thousand records its time consuming process,any other approach for this,..?

Add a Solution

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

Santhosh Kumar Jayaraman · Accepted Answer · 2012-09-25T22:42:00

Solution 2

i Believe you dont have any joining columns between 2 tables and for each row in table 1 you want to have multiple combinations from table2..
then try this

SQL

select SourceCodeId,SiteId from table1, table2 order by SourceCodeId

Posted 25-Sep-12 22:42pm

Santhosh Kumar Jayaraman

Updated 25-Sep-12 22:44pm

v2

Comments

sunandandutt 26-Sep-12 4:58am

My 5!

Shanalal Kasim 14-Nov-12 3:41am

My 5

Herman<T>.Instance 14-Nov-12 5:13am

my 5 too

Inserting a data from one table to another

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Inserting a data from one table to another

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us