Importing data using SQL job

Morpheus

Hi all,

I have to run a SQL job daily that will drop create 11 tables and insert data into each of them.

Currently ive one script with 11 blocks of code in it each written in a similiar format to this...

if table1 exists drop table1 

Create table1

Insert data from linked server.table2 to table1

I now have to wrap all of this in a transaction and schedule it to run daily as a job.

Would one giant stored procedure transaction suffice
or
would it be better practice to have 11 stored procedures, one for each table insert, with each created as a transaction and then one master stored procedure which calls each of these in turn (they must execute in a specific order)

your thoughts/comments?

Beano

Just do it from one stored proc. I cant see any benefit to breaking it up. Why do you need to drop the table and create it again? Why not just truncate the table?

bonkey

My first thought/comment would be to ask if you are aware that DDL (drop/create in this case) statements cannot be transacted?

So why you're wrapping the whole thing in a transaction is beyond me.

jc

Morpheus

why'd you hijack my thread bonkey???
[later that same day]
hmmm ... somethings afoot here, bonkeys post has changed... methinks that gremlins are in the system and someones been feeding them after midnight

Anywho, yeah its a strange one, i have to use transactions i suppose, the code will look like this...

Create procedure BigCrapProcedure

AS

IF EXISTS FirstTable DROP FirstTable

CREATE TABLE FirstTable as follows...

[b]START TRANSACTION[/b]
INSERT INTO FirstTable()
SELECT
a,
b,
c,
FROM
OtherServer.Database.dbo.SourceTable1
[b]END TRANSACTION[/b]

IF EXISTS SecondTable DROP SecondTable

CREATE TABLE SecondTable as follows...

[b]START TRANSACTION[/b]
INSERT INTO SecondTable()
SELECT
a,
b,
c,
FROM
OtherServer.Database.dbo.SourceTable2
[b]END TRANSACTION[/b]

IF EXISTS ThirdTable DROP ThirdTable

CREATE TABLE ThirdTable as follows...

[b]START TRANSACTION[/b]
INSERT INTO ThirdTable()
SELECT
a,
b,
c,
FROM
OtherServer.Database.dbo.SourceTable3
[b]END TRANSACTION[/b]

/* and so on for 11 different tables */

Now i plan to wrap each insert in a transaction, not the create table, however, if it makes more sense then i will delete all records from each table and simply repopulate it as follows

Create procedure BigCrapProcedure

AS

[b]START TRANSACTION[/b]

DELETE FirstTable

INSERT INTO FirstTable()
SELECT
a,
b,
c,
FROM
OtherServer.Database.dbo.SourceTable1


DELETE SecondTable

INSERT INTO SecondTable()
SELECT
a,
b,
c,
FROM
OtherServer.Database.dbo.SourceTable2



DELETE ThirdTable

INSERT INTO ThirdTable()
SELECT
a,
b,
c,
FROM
OtherServer.Database.dbo.SourceTable3

/* and so on for 11 different tables */

[b]END TRANSACTION[/b]

what you think?!

bonkey

Morph&#233 wrote: »

why'd you hijack my thread bonkey???

Huh? How'd I do that?

[later that same day]
hmmm ... somethings afoot here, bonkeys post has changed...

It still reads to me what I originally wrote.

Anywho, yeah its a strange one, i have to use transactions i suppose,

But *why* do you have to use transactions? They bring nothing to the proccess.

Consider two scenarios :

1) The table doesn't exist. So you create it. You populate it. If this fails somewhere, then on
re-run, you will hit scenario 2 if the problem occurred after the create.

2) The table does exist, so you drop/create it, and then populate it. If this fails, you rerun
from start, et voila...you drop the half-populated table and start over. Whats important to remember, however, is that once you drop a table, nothing short of a restore-from-backup can get you back to where you started.

So in the drop/create/populate approach, transactions bring nothing to the table (pun intended). so why use them?

The only possible reason I can see is that an empty created table is more easy to spot as an indicator of a problem than a partially-filled table...but to be honest, you should be checking whether or not the script/job succeeded or failed anyway, so I don't put much store by that one.

however, if it makes more sense then i will delete all records from each table and simply repopulate it as follows

Check whether you can use TRUNCATE TABLE instead of DELETE. It should be far more efficient*.

This delete/insert approach means that everything can be transacted, so that if something goes wrong, you can return to the state you were in before you started. Generally, I would say this is preferable to the drop/create route.

As an aside - in neither situation do you seem to be considering Indexes and/or Primary/Foreign keys on the tables. Do you not have any?



* This is actually dependant on what DB System you're using. Some systems simply map TRUNCATE to DELETE. I have a suspicion that some systems map it to 'Commit-and-then-delete'. The correct implementation is a minimally-logged delete. Efficiency, incidentally, is only really an issue if the table is reasonably big.

Morpheus

As an aside - in neither situation do you seem to be considering Indexes and/or Primary/Foreign keys on the tables. Do you not have any?

If i've understood this :rolleyes: correctly then I'm way ahead of you...

There are all of the above and in the drop create scenario i have scripted the tables to recreate indexes and constraints foreign keys etc and the 11 stored procedures must execute in a specific order because of this.

The idea is that these 11 tables are used as the source for a web GUI system that shows a snapshot of the previous 24hrs transactions, accounts and sales.

Have you any information on truncating? Oh i mean im not lazy im researching it now, but have you used it before for anything similiar?


Found this in SQL Query Analyser help.....

TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.

TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

So i guess you're right its simply a quicker form of delete....

Beano

Bonkey is correct in that a truncate can be more efficient but this depends on the database you are using. For MS SQL Server a truncate is definitely more efficient. However, if a table has a foreign key constraint then you cannot use a truncate, you must use a Delete From instead.

I still dont understand why this needs to be done in 11 stored procs. Why cant you use just one?

Morpheus

Im just using one in this format

Create procedure BigCrapProcedure

AS

START TRANSACTION

DELETE FirstTable

INSERT INTO FirstTable()
SELECT
a,
b,
c,
FROM
OtherServer.Database.dbo.SourceTable1


DELETE SecondTable

INSERT INTO SecondTable()
SELECT
a,
b,
c,
FROM
OtherServer.Database.dbo.SourceTable2



DELETE ThirdTable

INSERT INTO ThirdTable()
SELECT
a,
b,
c,
FROM
OtherServer.Database.dbo.SourceTable3

/* and so on for 11 different tables */

END TRANSACTION
what you think?!

Based on what you have said, i will use delete rather than truncate because there ARE foreign key contraints...

Morpheus

Hi, this wont work, the delete conflicts with foreign key constraints...

i may have to drop all of a tables foreign key constraints, then delete all of its records.

Now when i come to each of the tables referenced by the dropped constraints, I populate them and Alter them, adding back their foreign key contraints ???



or else go with the drop create scenario?

Beano

You probably just need to change the order of the deletes. If a field in table A had a FK constraint to a field in table B then you need to delete from table A before you delete from table B. If you get what I mean.

bonkey

Morph&#233 wrote: »

Hi, this wont work, the delete conflicts with foreign key constraints...

i may have to drop all of a tables foreign key constraints, then delete all of its records.

You should be able to just disable them, rather than dropping them. (The keyword in mssql is NOCHECK, I think...part of the ALTER TABLE command).

Now when i come to each of the tables referenced by the dropped constraints, I populate them and Alter them, adding back their foreign key contraints ???

Yup. Its a horrible thing to have to do, cause you're dependant on the new data still having all the correct, referenced values. Sometimes there's no choice, though.

or else go with the drop create scenario?

IIRC, you can't drop a table thats referenced by FKCs, so that won't solve anything....you'll still have to drop/recreate the constraints.

jc