Click here to Skip to main content
11,410,750 members (62,484 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server
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 22:28pm
Comments
Deepak Jena at 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 at 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?
Rate this: bad
good
Please Sign up or sign in to vote.

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

select SourceCodeId,SiteId from table1, table2 order by SourceCodeId
  Permalink  
v2
Comments
sunandandutt at 26-Sep-12 4:58am
   
My 5!
Shanalal Kasim at 14-Nov-12 3:41am
   
My 5
digimanus at 14-Nov-12 5:13am
   
my 5 too
Rate this: bad
good
Please Sign up or sign in to vote.

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):
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
  Permalink  
Comments
chetankumar1333 at 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,..?
Rate this: bad
good
Please Sign up or sign in to vote.

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,
(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
select Str(SourceCodeID,4,4) + Str(SiteID,3,3) as RESULT 
from SourceCodeID_Tbl, Site_tbl
where SourceCodeID = 1905
order by SourceCodeID
  Permalink  
v6

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 8,920
1 OriginalGriff 6,918
2 Maciej Los 3,390
3 Abhinav S 3,248
4 Peter Leow 3,059


Advertise | Privacy | Mobile
Web04 | 2.8.150414.5 | Last Updated 14 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100