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
Solved

Making an insert query more efficient

Posted on 2011-03-08
5
276 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
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:ewangoya
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pivot Query Problem 9 47
SQL Agent Timeout 5 64
Selection from table2 where criteria for table1 10 49
Sql Server group by 10 44
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

839 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