Is “SET IDENTITY_INSERT” required to insert arbitrary GUID into [uniqueidentifier] column?












2















SQL Server 2016 and 2017, Standard Edition



I need to insert raw data (from another table) into a table that has two uniqueidentifier columns.



Does this require that I use SET IDENTITY_INSERT Carrier ON?



Here is the table:



SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Parkingspace](
[ParkingspaceId] [uniqueidentifier] NOT NULL,
[AccountId] [uniqueidentifier] NULL,
[ParkingspaceType] [smallint] NULL,
CONSTRAINT [PK_ParkingspaceId] PRIMARY KEY CLUSTERED
(
[ParkingspaceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]


Will the following work without the IDENTITY_INSERT option? (I think it should but the DBA here is very married to the IDENTITY_INSERT option.)



INSERT INTO Parkingspace 
(ParkingspaceID,AcountID,ParkingspaceType)
SELECT * FROM PRODDBCopy.Parkingspace









share|improve this question





























    2















    SQL Server 2016 and 2017, Standard Edition



    I need to insert raw data (from another table) into a table that has two uniqueidentifier columns.



    Does this require that I use SET IDENTITY_INSERT Carrier ON?



    Here is the table:



    SET ANSI_NULLS ON

    SET QUOTED_IDENTIFIER ON

    CREATE TABLE [dbo].[Parkingspace](
    [ParkingspaceId] [uniqueidentifier] NOT NULL,
    [AccountId] [uniqueidentifier] NULL,
    [ParkingspaceType] [smallint] NULL,
    CONSTRAINT [PK_ParkingspaceId] PRIMARY KEY CLUSTERED
    (
    [ParkingspaceId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]


    Will the following work without the IDENTITY_INSERT option? (I think it should but the DBA here is very married to the IDENTITY_INSERT option.)



    INSERT INTO Parkingspace 
    (ParkingspaceID,AcountID,ParkingspaceType)
    SELECT * FROM PRODDBCopy.Parkingspace









    share|improve this question



























      2












      2








      2








      SQL Server 2016 and 2017, Standard Edition



      I need to insert raw data (from another table) into a table that has two uniqueidentifier columns.



      Does this require that I use SET IDENTITY_INSERT Carrier ON?



      Here is the table:



      SET ANSI_NULLS ON

      SET QUOTED_IDENTIFIER ON

      CREATE TABLE [dbo].[Parkingspace](
      [ParkingspaceId] [uniqueidentifier] NOT NULL,
      [AccountId] [uniqueidentifier] NULL,
      [ParkingspaceType] [smallint] NULL,
      CONSTRAINT [PK_ParkingspaceId] PRIMARY KEY CLUSTERED
      (
      [ParkingspaceId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
      ) ON [PRIMARY]


      Will the following work without the IDENTITY_INSERT option? (I think it should but the DBA here is very married to the IDENTITY_INSERT option.)



      INSERT INTO Parkingspace 
      (ParkingspaceID,AcountID,ParkingspaceType)
      SELECT * FROM PRODDBCopy.Parkingspace









      share|improve this question
















      SQL Server 2016 and 2017, Standard Edition



      I need to insert raw data (from another table) into a table that has two uniqueidentifier columns.



      Does this require that I use SET IDENTITY_INSERT Carrier ON?



      Here is the table:



      SET ANSI_NULLS ON

      SET QUOTED_IDENTIFIER ON

      CREATE TABLE [dbo].[Parkingspace](
      [ParkingspaceId] [uniqueidentifier] NOT NULL,
      [AccountId] [uniqueidentifier] NULL,
      [ParkingspaceType] [smallint] NULL,
      CONSTRAINT [PK_ParkingspaceId] PRIMARY KEY CLUSTERED
      (
      [ParkingspaceId] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
      ) ON [PRIMARY]


      Will the following work without the IDENTITY_INSERT option? (I think it should but the DBA here is very married to the IDENTITY_INSERT option.)



      INSERT INTO Parkingspace 
      (ParkingspaceID,AcountID,ParkingspaceType)
      SELECT * FROM PRODDBCopy.Parkingspace






      sql-server sql-server-2016 sql-server-2017 identity uniqueidentifier






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Feb 13 at 22:13









      Paul White

      53.1k14283457




      53.1k14283457










      asked Feb 13 at 22:05









      samsmithsamsmith

      1,55522144




      1,55522144






















          2 Answers
          2






          active

          oldest

          votes


















          7














          IDENTITY_INSERT is only used for columns with the IDENTITY property.



          UNIQUEIDENTIFIER is merely a data type.



          Your insert statement will work.






          share|improve this answer































            0














            Identity inserts only apply to identity columns in sql server trying to create non numeric identity columns will produce this error:




            Identity column 'id' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, unencrypted, and constrained to be nonnullable.




            No uniqueidentifiers allowed.






            share|improve this answer























              Your Answer








              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "182"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              autoActivateHeartbeat: false,
              convertImagesToLinks: false,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: null,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














              draft saved

              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229673%2fis-set-identity-insert-required-to-insert-arbitrary-guid-into-uniqueidentifie%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              7














              IDENTITY_INSERT is only used for columns with the IDENTITY property.



              UNIQUEIDENTIFIER is merely a data type.



              Your insert statement will work.






              share|improve this answer




























                7














                IDENTITY_INSERT is only used for columns with the IDENTITY property.



                UNIQUEIDENTIFIER is merely a data type.



                Your insert statement will work.






                share|improve this answer


























                  7












                  7








                  7







                  IDENTITY_INSERT is only used for columns with the IDENTITY property.



                  UNIQUEIDENTIFIER is merely a data type.



                  Your insert statement will work.






                  share|improve this answer













                  IDENTITY_INSERT is only used for columns with the IDENTITY property.



                  UNIQUEIDENTIFIER is merely a data type.



                  Your insert statement will work.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Feb 13 at 22:09









                  Paul WhitePaul White

                  53.1k14283457




                  53.1k14283457

























                      0














                      Identity inserts only apply to identity columns in sql server trying to create non numeric identity columns will produce this error:




                      Identity column 'id' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, unencrypted, and constrained to be nonnullable.




                      No uniqueidentifiers allowed.






                      share|improve this answer




























                        0














                        Identity inserts only apply to identity columns in sql server trying to create non numeric identity columns will produce this error:




                        Identity column 'id' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, unencrypted, and constrained to be nonnullable.




                        No uniqueidentifiers allowed.






                        share|improve this answer


























                          0












                          0








                          0







                          Identity inserts only apply to identity columns in sql server trying to create non numeric identity columns will produce this error:




                          Identity column 'id' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, unencrypted, and constrained to be nonnullable.




                          No uniqueidentifiers allowed.






                          share|improve this answer













                          Identity inserts only apply to identity columns in sql server trying to create non numeric identity columns will produce this error:




                          Identity column 'id' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, unencrypted, and constrained to be nonnullable.




                          No uniqueidentifiers allowed.







                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Feb 14 at 0:27









                          jmorenojmoreno

                          644516




                          644516






























                              draft saved

                              draft discarded




















































                              Thanks for contributing an answer to Database Administrators Stack Exchange!


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid



                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.


                              To learn more, see our tips on writing great answers.




                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f229673%2fis-set-identity-insert-required-to-insert-arbitrary-guid-into-uniqueidentifie%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              Probability when a professor distributes a quiz and homework assignment to a class of n students.

                              Aardman Animations

                              Are they similar matrix