Data loading is incomplete -

Question

Data loading is incomplete -

ramana3327

SSCoach

Points: 19722
More actions
January 7, 2017 at 2:18 pm

#333663

Hi,
We have a SQL Job that truncate tables in one database then insert into the tables from different database. Some times we are missing some data with in the destination tables even though data is exists in the source tables.
Do we need to put
TRUNCATE TABLE TestDB2.DBA.Test1
Truncate Table TestDB2.DBA.Test2
INSERT TestDB2.DBA.Test1
(col1,
col2,
col3,
col4
)
SELECT col1, col2, col3,col4
FROM [TestDB1].[dbo].[Test1]
INSERT TestDB2.DBA.Test2
(col1,
col2,
col3
)
SELECT col1, col2, col3
FROM [TestDB1].[dbo].[Test2]

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Do you have a question?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

ramana3327 (1/7/2017)
Hi,
We have a SQL Job that truncate tables in one database then insert into the tables from different database. Some times we are missing some data with in the destination tables even though data is exists in the source tables.
Do we need to put
TRUNCATE TABLE TestDB2.DBA.Test1
Truncate Table TestDB2.DBA.Test2
INSERT TestDB2.DBA.Test1
(col1,
col2,
col3,
col4
)
SELECT col1, col2, col3,col4
FROM [TestDB1].[dbo].[Test1]
INSERT TestDB2.DBA.Test2
(col1,
col2,
col3
)
SELECT col1, col2, col3
FROM [TestDB1].[dbo].[Test2]

It appears that the data in these two tables is expendable. Consider setting it all up for "Minimal Logging" so that it's faster and less resource intensive. Google is your friend on the "Minimal Logging" thing and it's very well worth learning about.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramana3327 SSCoach Points: 19722 More actions · Answer 3

We are using the database in simple recovery. I believe simple recovery model is also minimally logged.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

ramana3327 - Thursday, February 9, 2017 6:46 AM
We are using the database in simple recovery. I believe simple recovery model is also minimally logged.

They are not the same thing. Read this article to improve your understanding of minimal logging, as your Googling skills appear to have deserted you.