将数据从一个表插入到另一个表 [英] Inserting a data from one table to another

问题描述

我有一个senario，我想在其中插入一个sourcecodeid
对于exm:
第一张桌子
SourceCodeId
1905
1910
1916
1918
1919

第二张桌子

SiteId
56
101
105
106
119
141

结果应该是
1905 56
1905 101
1905 105
1905 106
1905 119
1905 141


像这样，我希望结果plz可以帮助我.

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.

推荐答案

i我相信您在2个表之间没有任何连接列，并且对于表1中的每一行都希望有多个表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

如Deepak Jena所说:"那里应该是2个表之间的关系！

例如，从两个表中创建另一个包含ID的表(我使用临时表):

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

您所说的我已经创建了2张桌子

1.第一个表"SourceCode_Tbl"包含具有值
的SourceCodeID列 1905
1910
1916
1918
1919

2.第二个表"Site_tbl"包含包含值的SiteID列
56
101
105
106
119
141

现在查询选择
1905 56
1905 101
1905 105
1905 106
1905 119
1905 141
会是

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

这篇关于将数据从一个表插入到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！