T-SQL insert into select from another database with subquery

Question

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?

Donal · Accepted Answer · 2015-03-20 15:56:19Z

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.

edited Mar 20, 2015 at 15:56

answered Mar 20, 2015 at 15:47

Donal

33.2k10 gold badges69 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pmbAustin · Accepted Answer · 2015-03-20 15:48:45Z

0

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

answered Mar 20, 2015 at 15:48

pmbAustin

4,0101 gold badge27 silver badges35 bronze badges

Comments

Donal · Accepted Answer · 2015-03-20 15:56:59Z

0

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

edited Mar 20, 2015 at 15:56

Donal

33.2k10 gold badges69 silver badges75 bronze badges

answered Mar 20, 2015 at 15:47

Chris Steele

1,3811 gold badge9 silver badges21 bronze badges

1 Comment

Chris Steele Over a year ago

I was about to post something snarky, Donal... but our answers are so similar, I realize you must have thought you were editing your own... heh! I've upvoted yours because you beat me to it.

Collectives™ on Stack Overflow

T-SQL insert into select from another database with subquery

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related