Solved

SQL Query Syntax question

Posted on 2014-12-03
3
155 Views
Last Modified: 2014-12-04
Hey all,

What is a cleaner way to do this query.  I am inserting 2 records here into this temp table but I am not wanting the union if possible as I am just increasing the date by + 1.

  INSERT INTO #TempCDCInsertData
               (
                     PortfolioId,
                     MVDate,
                     Amount,
                     AmountUSD,
                     ReconcileFlag,
                     ISOCurrencyCode,
                     start_lsn,
                     tran_end_time,
                     operation,
                     CDCInstanceID
               )
               SELECT
                     PortfolioId,
                     MVDate + 1,
                     Amount,
                     AmountUSD,
                     ReconcileFlag,
                     ISOCurrencyCode,
                     start_lsn,
                     tran_end_time,
                     operation,
                     CDCInstanceID
               FROM #TempCDCInsertData
               UNION ALL
               SELECT
                     PortfolioId,
                     MVDate + 2,
                     Amount,
                     AmountUSD,
                     ReconcileFlag,
                     ISOCurrencyCode,
                     start_lsn,
                     tran_end_time,
                     operation,
                     CDCInstanceID
               FROM #TempCDCInsertData
0
Comment
Question by:sbornstein2
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40479346
I don't know about 'cleaner', but...

Declare @x int = 1 

WHILE @x <= 2
begin

INSERT INTO #TempCDCInsertData
               (
                     PortfolioId, 
                     MVDate, 
                     Amount,
                     AmountUSD, 
                     ReconcileFlag,
                     ISOCurrencyCode, 
                     start_lsn, 
                     tran_end_time, 
                     operation, 
                     CDCInstanceID
               )
               SELECT 
                     PortfolioId, 
                     DATEADD(day, @x, MVDate ) , 
                     Amounnt, 
                     AmountUSD, 
                     ReconcileFlag,
                     ISOCurrencyCode, 
                     start_lsn, 
                     tran_end_time, 
                     operation, 
                     CDCInstanceID
 FROM #TempCDCInsertData

SET @x = @x + 1

end

Open in new window

0
 

Author Closing Comment

by:sbornstein2
ID: 40480695
thanks
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40480705
Yep that's useful whenever you need to insert/update/whatever @x number of rows.

Thanks for the grade, this gets me to 7 million.  Good luck with your project.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Querying data from 3 SQL tables 2 32
T-SQL: "HAVING CASE" Clause 1 25
what are the unique tables in SQL master database 5 62
replace \ by - in select 4 21
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

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