[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL Query Syntax question

Posted on 2014-12-03
3
Medium Priority
?
171 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 66

Accepted Solution

by:
Jim Horn earned 2000 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 66

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

591 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