将数据从一个表插入到另一个表

问题描述

我有一个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.

推荐答案

select SourceCodeId,SiteId from table1, table2 order by SourceCodeId

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

(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

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