Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Making an insert query more efficient

Posted on 2011-03-08
5
Medium Priority
?
284 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

618 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