Well I have been Moved to the DBA Team and I am a Noob

Question

Well I have been Moved to the DBA Team and I am a Noob

jameslima

Grasshopper

Points: 14
More actions
July 24, 2016 at 6:46 pm

#320119

Hi,
I have been moved from desktop support to the dba team as a junior dba and I have zero experience.
If its not too much trouble can I get a little help from a simple project I was given?
I am supposed to take a flat file from a window share and import the data into a Table. I have managed to get a job to run an SSIS package that does this (after some trial and error).
They want to me to set up a regularly scheduled job that does this, but here is the issue. They want me to first check if the table has data (which it should have), and if so, take THAT data and move it (also delete it) into an archive table (but not erase the data in the archive and add the data to the data in the archive). Then the job/script or whatever will import the data from the flat file.
I am clueless on the approach to take for this.
Thank you for helping a super noob! 😀

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

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

pietlinden SSC Guru Points: 63428 More actions · Answer 1

I'm not sure it matters if the table has data or not...

If you run some TSQL to append the data to the Archive table, and then run another that truncates the holding table, you're off to the races.

There's no point in checking to see if there's data in the table... if there's nothing there, nothing will be appended and deleted. And since that doesn't hurt anything, so what?

jameslima Grasshopper Points: 14 More actions · Answer 2

I was thinking that it might have data since this is going to be a regular thing. They want to run the job every month, so the table should have data in it from the previous flat file. At least I think so...maybe I am wrong? I am new at this.

They want the table to only have the current data from the flat file but not the previous month or something.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

jameslima (7/24/2016)
I was thinking that it might have data since this is going to be a regular thing. They want to run the job every month, so the table should have data in it from the previous flat file. At least I think so...maybe I am wrong? I am new at this.
They want the table to only have the current data from the flat file but not the previous month or something.

You could test the staging table to see if it has data in it or not by using [font="Courier New"]IF EXISTS (SELECT * FROM dbo.Staging Table)[/font] but it doesn't actually matter if the staging table has data in it or not. An [font="Courier New"]INSERT INTO dbo.Archive Table FROM dbo.StagingTable [/font]will work just fine whether the staging table has anything in it or not and it will require one less "hit" in doing so. Once that's successful, then just TRUNCATE the staging table whether it had rows in it or not. It will also be successful. After that, load the staging table with data as you have already done.

--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)

bogusdude Newbie Points: 3 More actions · Answer 4

Will something like this work?

Insert into [dbo].[Linux_Collections_Archive]

SELECT *

from [dbo].[Linux_Server_Collections]

USE [MSB_Data]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Linux_Server_Collections]') AND type in (N'U'))

TRUNCATE TABLE [dbo].[Linux_Server_Collections]

GO

USE [MSB_Data]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO