Post reply

Query help required

  • November 16, 2009 at 9:55 am

    #216801

    Hi,

    I have SQL Server Management Studio 2005 and need help copying a .dbo.

    I'd like to copy the complete dbo.Comments from the NDM-MWD database to the NDP-MWD database. Both databases are in use and have identical structures.

    I have attached a screenshot to facilitate the query creation.

    Thanks!!

  • November 16, 2009 at 10:04 am

    #1079780

    copy the complete dbo.Comments from the NDM-MWD database to the NDP-MWD database

    Use the Following Script

    INSERT INTO NDP-MWD.dbo.Comments Select * FROM NDM-MWD.dbo.Comments

    You need to take additional care if there is any Identity Column

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • November 16, 2009 at 10:16 am

    #1079784

    Hi,

    Thanks for the reply.

    I first had syntax errors and had to change it to: INSERT INTO [NDP-MWD].[dbo].[Comments] SELECT * FROM [NDM-MWD].[dbo].[Comments]

    Now I'm getting this message:

    Msg 8101, Level 16, State 1, Line 1

    An explicit value for the identity column in table 'NDP-MWD.dbo.Comments' can only be specified when a column list is used and IDENTITY_INSERT is ON.

  • November 16, 2009 at 10:57 am

    #1079806

    That's what I meant in my last reply.

    In the Query Editor,

    Use this

    USE

    [NDP-MWD]

    SET IDENTITY_INSERT [NDP-MWD].dbo.[Comments] ON

    INSERT INTO NDP-MWD.dbo.Comments Select * FROM NDM-MWD.dbo.Comments

    SET IDENTITY_INSERT [NDP-MWD].dbo.[Comments] OFF

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • November 16, 2009 at 11:32 am

    #1079825

    Unfortunately i'm getting the same error!

    Msg 8101, Level 16, State 1, Line 6

    An explicit value for the identity column in table 'NDP-MWD.dbo.Comments' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    Am I doing something wrong or how do I turn Identity Insert On ??!??

    Thanks!

  • November 16, 2009 at 12:06 pm

    #1079852

    Change the Query and include the Columns Names like this

    USE

    [NDP-MWD]

    SET IDENTITY_INSERT [NDP-MWD].dbo.[Comments] ON

    INSERT INTO NDP-MWD.dbo.Comments ( COL1,COL2,COL3,COL4........ )

    Select COL1,COL2,COL3,COL4........ FROM NDM-MWD.dbo.Comments

    SET IDENTITY_INSERT [NDP-MWD].dbo.[Comments] OFF

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply