Data loading is incomplete -

Question

Data loading is incomplete -

ramana3327

SSCoach

Points: 19572
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: 245813 More actions · Answer 1

Do you have a question?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1000539 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)
Intro to Tally Tables and Functions

ramana3327 SSCoach Points: 19572 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: 245813 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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

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.

Nope. That's not all you have to do. Please see the link that Phil pointed you to. And, to be sure, the DB doesn't need to be in the SIMPLE Recovery Model to do minimal logging. It can also be in the BULK Logged Recovery Model and requires the same attention to detail that Minimal Logging requires in the SIMPLE Recovery Model.

--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)
Intro to Tally Tables and Functions