4

I'm trying to copy a record from TableA back to TableA, but using a new Identity. I don't want to specify column list as I have over 100 columns, and there may be more in the future. Id like a chunk of code that can run when/if things change.

After looking similar questions, I have attempted this code

SELECT * INTO #tmp FROM TableA WHERE Id = 1;
    ALTER TABLE #tmp DROP COLUMN Id;
    INSERT INTO TableA SELECT * FROM #tmp;
    Drop Table #tmp;

I am however still getting this error

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

Running a Select * FROM #tmp gives me what I would expect. A single record with all my Columns with the exception of the Id column.

Any Ideas?

Thanks!

EDIT Here is a pictures of the properties of the Id Column

enter image description here

Dan Field
  • 20,885
  • 5
  • 55
  • 71
user2684037
  • 69
  • 1
  • 9
  • I believe you will find your answer here: http://stackoverflow.com/a/2005449/3044080 – nomistic Jun 02 '15 at 16:49
  • I have already looked at that page. I am not trying to do an Identity Insert. I would still like the Id column to auto generate its next number. – user2684037 Jun 02 '15 at 16:52
  • Are you sure that `Id` is, in fact, the Identity column for that table? – Douglas Barbin Jun 02 '15 at 16:53
  • I just updated the post to include the properties of the Id column. I am positive its the only Primary Key, and is an Identity. – user2684037 Jun 02 '15 at 16:56
  • There are a few tools that can do autocomplete like statements for updates/inserts that could get you what you're asking for. type "insert into [table]" and all columns are pulled. Not clear on if you wanted a stored procedure to do this, or if a script is sufficient. But you can look into ApexSql (free) or RedGate (not free, but I find more useful) to help with autocompleting insert/update statements like this. – Kritner Jun 02 '15 at 16:59
  • I'm afraid SQL Server is very restrictive on this issue. Even you set the identity_insert on , you will not be able to use "*" for the insertion. Your solution is to build your query dynamically using the column list. – Tim3880 Jun 02 '15 at 17:02

2 Answers2

4

Use Dynamic SQL: get your list of columns (except ID), build an insert statement using that list, and then call exec on it:

SELECT * 
  INTO #tmp 
  FROM TableA 
  WHERE Id = 1;
ALTER TABLE #tmp DROP COLUMN id;

DECLARE @cols varchar(max);

SELECT 
  @cols = COALESCE(@cols + ',', '') + COLUMN_NAME 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'TableA' AND COLUMN_NAME <> 'id'

--select @cols -- display column list for debugging
DECLARE @sqltxt varchar(max) = 'INSERT INTO TableA (' + @cols + ') SELECT * FROM #tmp';

--SELECT @sqltxt -- display the statement for debugging

exec (@sqltxt)

DROP TABLE #tmp
Dan Field
  • 20,885
  • 5
  • 55
  • 71
  • THANK YOU! Interestingly enough, I think my issue might have been stemming from the fact that I have 2 computed columns (that I had forgotten about). Tweaking this to also remove the computed columns from the insert did the job beautifully! This actually gave me a useful error telling me I have computed columns. – user2684037 Jun 02 '15 at 17:23
  • @Kritner, Unless I'm misunderstanding you no this should be fine. We're not creating a temp table with dynamic SQL, just reading from it. If we created it with dynamic SQL that could cause issues (I think). At any rate, I've tested this and it works. – Dan Field Jun 02 '15 at 18:04
1

Try This

Step 1 :

INSERT INTO Employee1(FirstName,LastName,ManagerID,Salary)
    SELECT FirstName,LastName,ManagerID,Salary
    FROM Employee1
    WHERE EmployeeID=X -- Your Emplyee ID

Step 2:

DELETE FROM Employee1 WHERE EmployeeID=X
sqluser
  • 5,502
  • 7
  • 36
  • 50
user4966675
  • 11
  • 1