2

I want to create e temp table using select into syntax. Like: 

select top 0 * into #AffectedRecord from MyTable

Mytable has a primary key. When I insert record using merge into syntax primary key be a problem. How could I drop pk constraint from temp table

seyfside
  • 117
  • 1
  • 7
  • Are you talking about an `IDENTITY` PK? If so, have a look at : http://stackoverflow.com/questions/7063501/how-to-turn-identity-insert-on-and-off-using-sql-server-2008#7063527 . No need to drop constraints. – Jens Mar 24 '17 at 14:04
  • I cant use this. Error message: An explicit value for the identity column in table '#AffectedRecord' can only be specified when a column list is used and IDENTITY_INSERT is ON. – seyfside Mar 24 '17 at 14:09
  • I suggest you actually look at `IDENTITY_INSERT` – Jens Mar 24 '17 at 14:13
  • It would be helpful to know what you intend to do. Dropping any id column is very risky and can cause a host of other things if done without considering all the repercussion. My intention is to make sure we as a community advise you of the best way forward while ensuring we do not inadvertently cause more problems for yourself – Gouri Shankar Aechoor Mar 24 '17 at 15:06

1 Answers1

1

The "SELECT TOP (0) INTO.." trick is clever but my recommendation is to script out the table yourself for reasons just like this. SELECT INTO when you're actually bringing in data, on the other hand, is often faster than creating the table and doing the insert. Especially on 2014+ systems.

The existence of a primary key has nothing to do with your problem. Key Constraints and indexes don't get created when using SELECT INTO from another table, the data type and NULLability does. Consider the following code and note my comments:

USE tempdb -- a good place for testing on non-prod servers. 
GO

IF OBJECT_ID('dbo.t1') IS NOT NULL DROP TABLE dbo.t1;
IF OBJECT_ID('dbo.t2') IS NOT NULL DROP TABLE dbo.t2;
GO

CREATE TABLE dbo.t1
(
  id int identity primary key clustered,
  col1 varchar(10) NOT NULL,
  col2 int         NULL
);
GO

INSERT dbo.t1(col1) VALUES ('a'),('b');

SELECT TOP (0) 
  id, -- this create the column including the identity but NOT the primary key
  CAST(id AS int) AS id2, -- this will create the column but it will be nullable. No identity
  ISNULL(CAST(id AS int),0) AS id3, -- this this create the column and make it nullable. No identity.
  col1,
  col2 
INTO dbo.t2
FROM t1;

Here's the (cleaned up for brevity) DDL for the new table I created:

-- New table
CREATE TABLE dbo.t2
(
  id   int IDENTITY(1,1) NOT NULL,
  id2  int               NULL,
  id3  int               NOT NULL,
  col1 varchar(10)       NOT NULL,
  col2 int               NULL
);

Notice that the primary key is gone. When I brought in id as-is it kept the identity. Casting the id column as an int (even though it already is an int) is how I got rid of the identity insert. Adding an ISNULL is how to make a column nullable.

By default, identity insert is set to off here to this query will fail: INSERT dbo.t2 (id, id3, col1) VALUES (1, 1, 'x');

Msg 544, Level 16, State 1, Line 39
Cannot insert explicit value for identity column in table 't2' when IDENTITY_INSERT is set to OFF.

Setting identity insert on will fix the problem:

SET IDENTITY_INSERT dbo.t2 ON; 
INSERT dbo.t2 (id, id3, col1) VALUES (1, 1, 'x');

But now you MUST provide a value for that column. Note the error here:

INSERT dbo.t2 (id3, col1) VALUES (1, 'x');


Msg 545, Level 16, State 1, Line 51
Explicit value must be specified for identity column in table 't2' either when IDENTITY_INSERT is set to ON

Hopefully this helps.

On a side-note: this is a good way to play around with and understand how select insert works. I used a perm table because it's easier to find.

Alan Burstein
  • 7,770
  • 1
  • 15
  • 18