locked
How can I skip parts of my script? RRS feed

  • Question

  • As part of a larger script, I have a set of inserts that insert over 40,000 rows (zip codes). Every 100 rows, there is a GO statement.

    To make the script run faster on databases that already contain the zip codes, I would like to skip the zip code inserts if the rows are already in the table.

    I can write an If statement, but that does not seem to work correctly because of the GO statements.

    Is there a way I can add an IF and skip all of these rows without adding the IF around every 100 rows?

    Thanks for any tips!


    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

    Monday, April 15, 2013 3:58 PM

Answers

  • I am not sure why MERGE statement hung. May be you need to figure this out first.

    For the statements written this way I do not see a way to adjust except for making them just one statement for each 100 rows, e.g.

    ;with cteValues AS (SELECT 98 as ZipCodeId, N'99709' as ZipCode, N'FAIRBANKS' as City, N'Alaska' as StateName, N'AK' as StateAbbreviation, 1 as Status, CAST(0x00588C00F2360B00000000 AS DateTimeOffset) as UpdateDate, N'System' as UpdatingUserName
    UNION ALL
    SELECT ...)
    
    INSERT [dbo].[ZipCode] ([ZipCodeId], [ZipCode], [City], [StateName], [StateAbbreviation], [Status], [UpdateDate], [UpdatingUserName]) 
    SELECT * FROM cteValues V where not exists (select 1 from ZipCode Z where Z.ZipCodeId = V.ZipCodeId and Z.ZipCode = V.ZipCode)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Monday, April 15, 2013 4:23 PM
    • Marked as answer by Allen Li - MSFT Tuesday, April 23, 2013 9:33 AM
    Monday, April 15, 2013 4:22 PM
  • We took the conversation "off line" and did indeed determine that the MERGE statement "hangs" when processing 40,000+ rows. So I went back to the Insert statements.

    But my question was really whether there was a way to skip all of this in the case where the database already has all 40,000+ rows.

    The answer is "no". No unless I wanted to wrap each 100 INSERTS with an If clause because you cannot have GO statements in the middle of an If.


    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

    • Marked as answer by DeborahKMVP Tuesday, April 23, 2013 2:46 PM
    Tuesday, April 23, 2013 2:46 PM

All replies

  • It depends on how exactly your insert statements look like. The best way would have been to use MERGE statements instead so they would insert rows only if they do not already exist.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, April 15, 2013 4:07 PM
  • We use a MERGE statement for all of our other scripts. But it just *hung* for this one. This is for zip codes and there are over 40,000 rows. So we changed it to simple INSERT statements

    Here is what the insert statements look like:

    INSERT [dbo].[ZipCode] ([ZipCodeId], [ZipCode], [City], [StateName], [StateAbbreviation], [Status], [UpdateDate], [UpdatingUserName]) VALUES (98, N'99709', N'FAIRBANKS', N'Alaska', N'AK', 1, CAST(0x00588C00F2360B00000000 AS DateTimeOffset), N'System')
    INSERT [dbo].[ZipCode] ([ZipCodeId], [ZipCode], [City], [StateName], [StateAbbreviation], [Status], [UpdateDate], [UpdatingUserName]) VALUES (99, N'99710', N'FAIRBANKS', N'Alaska', N'AK', 1, CAST(0x00588C00F2360B00000000 AS DateTimeOffset), N'System')
    INSERT [dbo].[ZipCode] ([ZipCodeId], [ZipCode], [City], [StateName], [StateAbbreviation], [Status], [UpdateDate], [UpdatingUserName]) VALUES (100, N'99711', N'FAIRBANKS', N'Alaska', N'AK', 1, CAST(0x00588C00F2360B00000000 AS DateTimeOffset), N'System')
    GO
    print 'Processed 100 total records'
    


    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

    Monday, April 15, 2013 4:16 PM
  • I am not sure why MERGE statement hung. May be you need to figure this out first.

    For the statements written this way I do not see a way to adjust except for making them just one statement for each 100 rows, e.g.

    ;with cteValues AS (SELECT 98 as ZipCodeId, N'99709' as ZipCode, N'FAIRBANKS' as City, N'Alaska' as StateName, N'AK' as StateAbbreviation, 1 as Status, CAST(0x00588C00F2360B00000000 AS DateTimeOffset) as UpdateDate, N'System' as UpdatingUserName
    UNION ALL
    SELECT ...)
    
    INSERT [dbo].[ZipCode] ([ZipCodeId], [ZipCode], [City], [StateName], [StateAbbreviation], [Status], [UpdateDate], [UpdatingUserName]) 
    SELECT * FROM cteValues V where not exists (select 1 from ZipCode Z where Z.ZipCodeId = V.ZipCodeId and Z.ZipCode = V.ZipCode)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Monday, April 15, 2013 4:23 PM
    • Marked as answer by Allen Li - MSFT Tuesday, April 23, 2013 9:33 AM
    Monday, April 15, 2013 4:22 PM
  • We took the conversation "off line" and did indeed determine that the MERGE statement "hangs" when processing 40,000+ rows. So I went back to the Insert statements.

    But my question was really whether there was a way to skip all of this in the case where the database already has all 40,000+ rows.

    The answer is "no". No unless I wanted to wrap each 100 INSERTS with an If clause because you cannot have GO statements in the middle of an If.


    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!

    • Marked as answer by DeborahKMVP Tuesday, April 23, 2013 2:46 PM
    Tuesday, April 23, 2013 2:46 PM