Copy and change a record---sql 2005

SQL Server Administration
1

I am trying what I though was simple, copy a record, change a field and paste it back into the table. I am using the Pubs database for practice. I copied an example and modified it. Thought I was home free- this is the code

CREATE table #MyTemp AS SELECT * FROM dbo.jobs WHERE job_id =14;
UPDATE #MyTemp SET job_desc = 'Big Pest' where job_id = 14;
UPDATE #MyTemp SET job_id = null;
INSERT INTO dbo.jobs SELECT * FROM #MyTemp;
DROP TABLE #MyTemp;

I get an incorrect syntax near 'AS'
Please help, I just shoveled 30" of snow from a storm that predicted 6-8" so I'm felling low enough.

2

CREATE TABLE ... AS is not SQL Server syntax, that's for a different dbms.

SELECT * INTO #MyTemp FROM dbo.jobs WHERE job_id =14;
UPDATE #MyTemp SET job_desc = 'Big Pest' where job_id = 14;
UPDATE #MyTemp SET job_id = null;
--TRUNCATE TABLE dbo.jobs --??
INSERT INTO dbo.jobs SELECT * FROM #MyTemp;
DROP TABLE #MyTemp;

3

This could be simplified to:

INSERT INTO dbo.jobs
SELECT job_id = Null, job_desc = 'Big Pest', {list all columns}
WHERE job_id = 14;

If the job_id column is an IDENTITY then you need to change both to include the list of columns in the INSERT and the SELECT.

INSERT INTO dbo.jobs (list of columns here - excluding job_id)
SELECT {list of columns here - excluding job_id}
WHERE job_id = 14;

4

I just got in and haven’t tried your solution yet but THANK YOU, THANK YOU, THANK YOU.
Seeing this in my inbox made my day. And just so you know, this isn’t just an exercise, I need to modify about 40,000 attendance records and the non profit I am working for desparately needs the money so thank you.

Michael

Ps- When I can do this with one record I’ll move on to several so you might not be through with me.

5

I just got in and haven’t tried your solution yet but THANK YOU, THANK YOU, THANK YOU.
Seeing this in my inbox made my day. And just so you know, this isn’t just an exercise, I need to modify about 40,000 attendance records and the non profit I am working for desparately needs the money so thank you.

Michael

Ps- When I can do this with one record I’ll move on to several so you might not be through with me.

6

I used your code after 20 minutes of wondering what the Truncate line meant. Finally it dawned on me it was a suggestion and not part of the code. I am really pre-school when it comes to this stuff. But when I ran it I got back the error can't insert identity. So I researched and this was the last version I tried

SET IDENTITY_INSERT dbo.jobs on;
SELECT job_id job_desc,min_lvl,max_lvl INTO #MyTemp FROM dbo.jobs WHERE job_id =14;
UPDATE #MyTemp SET job_desc = 'Big Pest' where job_id = 14;
UPDATE #MyTemp SET job_id = null;
INSERT INTO dbo.jobs SELECT job_id,job_desc,min_lvl,max_lvl FROM #MyTemp;
DROP TABLE #MyTemp;

The error message is
Msg 8102, Level 16, State 1, Line 6
Cannot update identity column 'job_id'.
Msg 8101, Level 16, State 1, Line 7
An explicit value for the identity column in table 'dbo.jobs' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I feel I am so close, that somewhere there is a stupid little something that I can't see and so obvious that I won't believe I didn't see it, but for now I'm lost.

7

I tried your code, I love it because it is so simple but you're giving me credit for being smarter than I am so thank you for that. The job_id column is an identity and this is what I tried:

INSERT INTO dbo.jobs (job_desc,min_lvl,max_lvl)
SELECT job_id = Null, job_desc = 'Big Pest', min_lvl,max_lvl
WHERE job_id = 14;

This was the error message:
Msg 121, Level 15, State 1, Line 1
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

I deleted the two columns I wasn't changing from the select statement and that didn't work either so I am still very, very lost.

thanks for the help and for responding,

michael

8

INSERT INTO dbo.jobs (job_desc,min_lvl,max_lvl)
SELECT job_desc = 'Big Pest', min_lvl, max_lvl
FROM dbo.jobs
WHERE job_id = 14;

If there are more columns in the table - and those columns do not have defined default values - you need to include them in both the insert and select.