locked
Generate Insert Script With Go For Every Insert Statement RRS feed

  • Question

  • Hi friends ,

    i just want to generate insert script in sql server , but for every insert statement i need one "Go" at the end of the script.

    is there any way to generate like this

    please if any one know this reply me.

    Thanks For your help

    Thursday, April 19, 2012 7:23 AM

Answers

  • When you use the SSMS Script Wizard, you get GO after each INSERT:

    USE [AdventureWorks2012]
    GO
    SET IDENTITY_INSERT [HumanResources].[Department] ON 
    
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (1, N'Engineering', N'Research and Development', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (2, N'Tool Design', N'Research and Development', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (3, N'Sales', N'Sales and Marketing', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (4, N'Marketing', N'Sales and Marketing', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (5, N'Purchasing', N'Inventory Management', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (6, N'Research and Development', N'Research and Development', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (7, N'Production', N'Manufacturing', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (8, N'Production Control', N'Manufacturing', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (9, N'Human Resources', N'Executive General and Administration', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (10, N'Finance', N'Executive General and Administration', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (11, N'Information Services', N'Executive General and Administration', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (12, N'Document Control', N'Quality Assurance', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (13, N'Quality Assurance', N'Quality Assurance', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (14, N'Facilities and Maintenance', N'Executive General and Administration', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (15, N'Shipping and Receiving', N'Inventory Management', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (16, N'Executive', N'Executive General and Administration', CAST(0x0000921E00000000 AS DateTime))
    GO
    SET IDENTITY_INSERT [HumanResources].[Department] OFF
    GO
    


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Thursday, November 29, 2012 10:55 AM

All replies

  • Check  this ..

    http://vyaskn.tripod.com/code.htm

    you may have to modifying it slightly to add the Go..

    But may i ask why you need GO after every insert ..??

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Thursday, April 19, 2012 7:31 AM
  • Hi v.vt

    i am inserting more than 1000 tables per day through the generated insert script .

    if any primary key or any error is coming in the middle of this execution its stopping the insertion in the remaing tables

    i want to skip those table and remaing i need to insert in one short.

    and also i am expecting my code in .net not in vb can u have any idea on this..

    Thursday, April 19, 2012 8:43 AM
  • Have you considered using SSIS http://msdn.microsoft.com/en-us/library/ms141026.aspx  to import data?

    Thanks

    Sethu Srinivasan [MSFT]

    SQL Server

    http://blogs.msdn.com/sqlagent 

    Wednesday, October 31, 2012 5:34 PM
  • Yes you can, do the follow the step

    1.Open the Query in Management Studio.

    2.Then open Find and replace window

    3. in the find option check regular experssion box and unchek all

    4. In the find text type \n

    5 In the replace text type \n GO \n

    6.Click replace all

    You will get what you need.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Friday, November 2, 2012 5:36 AM
  • Any updates?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, November 2, 2012 8:40 AM
  • When you use the SSMS Script Wizard, you get GO after each INSERT:

    USE [AdventureWorks2012]
    GO
    SET IDENTITY_INSERT [HumanResources].[Department] ON 
    
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (1, N'Engineering', N'Research and Development', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (2, N'Tool Design', N'Research and Development', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (3, N'Sales', N'Sales and Marketing', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (4, N'Marketing', N'Sales and Marketing', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (5, N'Purchasing', N'Inventory Management', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (6, N'Research and Development', N'Research and Development', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (7, N'Production', N'Manufacturing', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (8, N'Production Control', N'Manufacturing', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (9, N'Human Resources', N'Executive General and Administration', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (10, N'Finance', N'Executive General and Administration', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (11, N'Information Services', N'Executive General and Administration', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (12, N'Document Control', N'Quality Assurance', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (13, N'Quality Assurance', N'Quality Assurance', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (14, N'Facilities and Maintenance', N'Executive General and Administration', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (15, N'Shipping and Receiving', N'Inventory Management', CAST(0x0000921E00000000 AS DateTime))
    GO
    INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (16, N'Executive', N'Executive General and Administration', CAST(0x0000921E00000000 AS DateTime))
    GO
    SET IDENTITY_INSERT [HumanResources].[Department] OFF
    GO
    


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Thursday, November 29, 2012 10:55 AM