CODEWITHSUNDEEP
sql-servert-sqlsubquery
maniek099
maniek099

Reputation: 339

T-SQL insert into select from another database with subquery

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?

Upvotes: 0

Views: 2769

Answers (3)

Chris Steele
Chris Steele

Reputation: 1381

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.

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).

Upvotes: 0

Donal
Donal

Reputation: 32813

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.

Upvotes: 1

pmbAustin
pmbAustin

Reputation: 3980

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:

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

Upvotes: 0

Related Questions