?
Solved

Making an insert query more efficient

Posted on 2011-03-08
5
Medium Priority
?
281 Views
Last Modified: 2012-05-11
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
Comment
Question by:mbs2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 1

Expert Comment

by:TheAnvilGroup
ID: 35069388
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
 
LVL 15

Expert Comment

by:derekkromm
ID: 35069406
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35069418
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
 
LVL 5

Accepted Solution

by:
idmedellin earned 2000 total points
ID: 35069734
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
 

Author Closing Comment

by:mbs2000
ID: 35073172
Spot on ... exactly what I was after....thanks very much
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question