Solved

Making an insert query more efficient

Posted on 2011-03-08
5
280 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 500 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

726 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