• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

Making an insert query more efficient

I have written TSQL code in insert two records from one database to another. There are 5 different records to insert into the same table and I find am having to supplier the Join section each time...is there a better way to trim the following code sample so that the join information is only used once preceeding each insert and select statement?
--First insert
INSERT [Testing].dbo.[IFC]

        ( [Field1],

          [Field2],

          [Field3],

          [Field4],

          [Field5]

        )
     

SELECT

      @SupplierInvoiceID,

      'Standing Charge',

      2,

      ROUND ([Standing_Charge],2),

      1

 
FROM MTest.dbo.Data D

INNER JOIN Points  AS P ON

            D.Point_Id = P.Id

INNER JOIN Testing.dbo.Meter M  ON

            P.M1_Code1 = M.MeterNumber COLLATE LATIN1_GENERAL_CI_AI

WHERE [SomeNumber] IS NOT NULL AND Code1 IS NOT NULL
and Date >= '2009-01-01'
 

--second insert
INSERT [Testing].dbo.[IFC]

        ( [Field1],

          [Field2],

          [Field3],

          [Field4],

          [Field5]

        )

SELECT

      @SupplierInvoiceID,

      'Other Charge',

      2,

      ROUND ([Other_Charge],2),

      1

     

FROM MTest.dbo.Data D

INNER JOIN Points  AS P ON

            D.Point_Id = P.Id

INNER JOIN Testing.dbo.Meter M  ON

            P.M1_Code1 = M.MeterNumber COLLATE LATIN1_GENERAL_CI_AI

WHERE [SomeNumber] IS NOT NULL AND Code1 IS NOT NULL
and Date >= '2009-01-01'
0
mbs2000
Asked:
mbs2000
1 Solution
 
TheAnvilGroupCommented:
Use a CASE statement in the SELECT.

CASE WHEN [YourCondition] = 1 THEN 'Standing Charge'
WHEN [YourCondition] = 2 THEN 'Other Charge'
ELSE 'Not Known'
END

Do this for the Field2 and Field4. The INSERT statement and JOINS will be the same.
0
 
derekkrommCommented:
INSERT [Testing].dbo.[IFC]

        ( [Field1],

          [Field2],

          [Field3],

          [Field4],

          [Field5]

        )
     

SELECT

      @SupplierInvoiceID,

      F2.F2,

      2,

case when F2.F2 = 'Standing Charge' then ROUND ([Standing_Charge],2) else ROUND([Other_Charge],2) end,

      1

 
FROM (select 'Standing Charge' F2 union select 'Other Charge' F2) f2
cross join (MTest.dbo.Data D

INNER JOIN Points  AS P ON

            D.Point_Id = P.Id

INNER JOIN Testing.dbo.Meter M  ON

            P.M1_Code1 = M.MeterNumber COLLATE LATIN1_GENERAL_CI_AI

WHERE [SomeNumber] IS NOT NULL AND Code1 IS NOT NULL
and Date >= '2009-01-01')
0
 
Ephraim WangoyaCommented:
declare @standing_charge float
declare @other_charge float

SELECT
      @standing_charge = ROUND ([Standing_Charge],2),
      @other_charge =  ROUND ([Other_Charge],2)
FROM MTest.dbo.Data D
INNER JOIN Points  AS P ON D.Point_Id = P.Id
INNER JOIN Testing.dbo.Meter M  ON P.M1_Code1 = M.MeterNumber COLLATE LATIN1_GENERAL_CI_AI
WHERE [SomeNumber] IS NOT NULL AND Code1 IS NOT NULL
and Date >= '2009-01-01'

INSERT [Testing].dbo.[IFC]
        ( [Field1],
          [Field2],
          [Field3],
          [Field4],
          [Field5]
        )
     

SELECT @SupplierInvoiceID, 'Standing Charge', 2, @standing_charge, 1
union all
SELECT @SupplierInvoiceID, 'Standing Charge', 2, @other_charge, 1
0
 
idmedellinCommented:
This is another way
SELECT Round ([Standing_Charge], 2) AS Standing_Charge, Round ([Other_Charge], 2) AS Other_Charge
INTO   #Charge
FROM   mtest.dbo.data d       INNER JOIN points AS p         ON d.point_id = p.id
       INNER JOIN testing.dbo.meter m         ON p.m1_code1 = m.meternumber COLLATE latin1_general_ci_ai
WHERE  [SomeNumber] IS NOT NULL       AND code1 IS NOT NULL       AND DATE >= '2009-01-01'  

INSERT [Testing].dbo.[IFC]       ([Field1],        [Field2],        [Field3],        [Field4],        [Field5])
SELECT @SupplierInvoiceID,       'Other Charge',       2,       Other_Charge,       1
FROM   #Charge

INSERT [Testing].dbo.[IFC]       ([Field1],        [Field2],        [Field3],        [Field4],        [Field5])
SELECT @SupplierInvoiceID,       'Standing Charge',       2,       Standing_Charge,       1
FROM   #Charge

DROP TABLE #Charge

Open in new window

0
 
mbs2000Author Commented:
Spot on ... exactly what I was after....thanks very much
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now