T-SQL插入到具有子查询的另一个数据库中的select

I would like to insert some data from one database to other with this query:

我想使用此查询从一个数据库向其他数据库插入一些数据:

USE [CostDatabase]
GO

INSERT INTO [dbo].[CostAllocationKeyElements]
           ([Guid]
           ,[Created]
           ,[CostAllocationKeyID]
           ,[CostCenterDefinitionID]
           ,[Amount])
     SELECT 
           DivisionKeyLineID,
           GETDATE(),
           DivisionKeyID,
           (SELECT TOP 1 Guid from [dbo].CostCenterDefinitions where CostCenterCode = CostCenterCode),
           GrantAmount
      FROM [TestDB].[dbo].[CSLSTDIVDivisionKeyLines]
GO

But the problem is with CostCenterCode, because I must insert Guid into CostCenterDefinitionID field, but in table CSLSTDIVDivisionKeyLines from database TestDB I have got only string code of CostCenterDefinition (CostCenterCode field), so I try to select Guid in subquery but in every row it selects only the same, first Guid from the table. Maybe the same names of columns in diferent databases are reason of that, but I don't think so. Can somebody tell me how can I fix that?

但问题在于CostCenterCode,因为我必须将Guid插入到CostCenterDefinitionID字段中,但是在数据库TestDB的表CSLSTDIVDivisionKeyLines中,我只得到了CostCenterDefinition(CostCenterCode字段)的字符串代码,所以我尝试在子查询中选择Guid,但是在它选择的每一行中只有相同,第一个Guid来自表。也许不同数据库中列的相同名称是其原因,但我不这么认为。有人能告诉我如何解决这个问题?

3 个解决方案

#1

You need to use aliases in your sub select. For example:

您需要在子选择中使用别名。例如:

 SELECT 
       [DivisionKeyLineID],
       GETDATE(),
       [DivisionKeyID],
       (SELECT TOP 1 ccd.[Guid] 
          FROM dbo.[CostCenterDefinitions] ccd 
          WHERE 
          ccd.[CostCenterCode] = dkl.[CostCenterCode]),
       [GrantAmount]
  FROM [TestDB].[dbo].[CSLSTDIVDivisionKeyLines] dkl

Without the alias I suspect it is just comparing the costcentrecode in CostCenterDefinitions with itself in your where clause.

如果没有别名,我怀疑它只是在您的where子句中将CostCenterDefinitions中的costcentrecode与其自身进行比较。

#2

SQL doesn't know which "CostCenterCode" you're referring to... so it's doing a self-equality check with the same column/same row/same table. You need to reference the outside table do to a "correlated subquery". Something like this:

SQL不知道你指的是哪个“CostCenterCode”......所以它正在使用相同的列/同一行/同一个表进行自我相等性检查。您需要将外部表做引用到“相关子查询”。像这样的东西:

INSERT INTO [dbo].[CostAllocationKeyElements]
           ([Guid]
           ,[Created]
           ,[CostAllocationKeyID]
           ,[CostCenterDefinitionID]
           ,[Amount])
     SELECT 
           c.DivisionKeyLineID,
           GETDATE(),
           c.DivisionKeyID,
           (SELECT TOP 1 Guid from [dbo].CostCenterDefinitions where CostCenterCode = c.CostCenterCode),
           c.GrantAmount
      FROM [TestDB].[dbo].[CSLSTDIVDivisionKeyLines] c
GO

#3

I think you need to set up aliases for your tables so that the subquery knows which CostCenterCode it's looking at in the comparison.

我认为您需要为表设置别名,以便子查询知道它在比较中查看哪个CostCenterCode。

SELECT 
           DivisionKeyLineID,
           GETDATE(),
           DivisionKeyID,
           (SELECT TOP 1 Guid 
              from [dbo].CostCenterDefinitions ccd 
              where 
                  ccd.CostCenterCode = cslst.CostCenterCode),
           GrantAmount
      FROM [TestDB].[dbo].[CSLSTDIVDivisionKeyLines] cslst

If you don't use the table aliases, it's just checking CostCenterCode from CostCenterDefinitions to itself, returning all rows in that table (which you then top 1 to get the same row every time).

如果您不使用表别名,则只需将CostCenterCode中的CostCenterCode检查为自身,然后返回该表中的所有行(然后您将从顶部1开始每次都获得相同的行)。

#1