Job To Delete All Records Then Copy All From Another Table

Question

Job To Delete All Records Then Copy All From Another Table

jre1229

SSC Rookie

Points: 26
More actions
April 22, 2015 at 7:05 am

#300096

I am using a SQL Server Agent jobs that run each morning to update the records in a table to match what they should be for that day. I built them and tested it using a test table called "testtable1". It worked fine. But when I switched over to our production table, it fails saying the table has to be decaled. What would be the difference. The production table has a "@" in front of the name, is that causing issues?
USE [Live_build]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
DELETE
FROM @ZIPLIST
INSERT INTO @ZIPLIST
SELECT * FROM tblZip3DSWed;
END

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

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

Lowell SSC Guru Points: 323547 More actions · Answer 1

@tables are table variables.

if you have a production table that starts with @, so that it deviates from standard sql naming conventions,you need to quotename the table:

USE [Live_build]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

BEGIN

DELETE

FROM [@ZIPLIST]

INSERT INTO [@ZIPLIST]

SELECT * FROM tblZip3DSWed;

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jre1229 SSC Rookie Points: 26 More actions · Answer 2

jre1229

SSC Rookie

Points: 26

April 22, 2015 at 7:26 am

#1792726

Bingo! Thanks bud

ScottPletcher SSC Guru Points: 101202 More actions · Answer 3

As an aside, if possible, TRUNCATE the table rather than use DELETE to reduce logging.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

jre1229 (4/22/2015)
I am using a SQL Server Agent jobs that run each morning to update the records in a table to match what they should be for that day. I built them and tested it using a test table called "testtable1". It worked fine. But when I switched over to our production table, it fails saying the table has to be decaled. What would be the difference. The production table has a "@" in front of the name, is that causing issues?
USE [Live_build]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
DELETE
FROM @ZIPLIST
INSERT INTO @ZIPLIST
SELECT * FROM tblZip3DSWed;
END

Do the tables [@ZIPLIST] and [tblZip3DSWed] have the same schema and indexes? Are the tables in the same filegroup?

If so, here is another method:

truncate table [@ZIPLIST];

alter table [tblZip3DSWed] switch to [@ZIPLIST];

Note, this will empty the table [tblZip3DSWed], so if you need to keep this data, my suggestion may not be the best.