copy data from staging table to production

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

copy data from staging table to production

Author

Topic

ziggy2015
Starting Member

11 Posts

Posted - 2015-04-12 : 07:02:48

Hi everyone,

I need your need help on T-SQL.

Iam trying to insert data from staging table to production table. In the staging table I only have period or date but no primary key.
This is my stging table

Create stagingtable(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)

Create Production(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)

I get this every month. What can I do to make sure only unique record are loaded into production table with no duplicate from previous month.

Thanks in advance

ziggy

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-12 : 14:26:23

you don't have a primary key in production either (not a good idea). first make one, then the insert be like this:


insert into production
select ...
from staging
where not exists 
(
    select 1 from staging
    where staging.key = production.key
)

ziggy2015
Starting Member

11 Posts

Posted - 2015-04-13 : 02:48:07

Thanks.

But can I used period as my primary key. The period relates to the month of the data, like Jan, feb, mar......

Alternatively, how do I generate primary key for both staging and production table.

Thanks in advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-13 : 08:48:01

You can use (almost) anything as a primary key as long as it is unique in the table. However I fear that in your case "period" may not be unique.

ziggy2015
Starting Member

11 Posts

Posted - 2015-04-25 : 03:51:56

Hi,
I am sorry for the late response.

I am still trying to create primary and foreign keys between staging and production table.
Create stagingtable(
Organisation_Code Varchar(20)
[Period] [char](7) NOT NULL,
Month Varchar(20),
Year Varchar(20),
[OrganisationDesc] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)

Create Production(
Organisation_Code Varchar(20)
[Period] [char](7) NOT NULL,
Month Varchar(20),
Year Varchar(20),
[OrganisationDesc] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)

The issue here is I am getting duplicates rows in the production table. How do I create primary key and foreign keys to make sure I don't load previous or existing records from staging table to production table. I am thinking a composite key with year, month and organization_code. But, I am not sure. In a nutshell, I get about two csv files in a month for the same organisation. Let say, April 15, I get one file and 30th of April, I get the latest of the refresh file, all going into the same table. I only want to retain the refresh values and delete the existing copy in the production table.

This is convoluted. Hope you understand me. Thanks for your time.
Ziggy

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-25 : 05:28:58

[code]/* primary keys MUST be of type "not null" */
alter table stagingtable
alter column organisation_code varchar(20) not null;
alter table stagingtable
alter column month varchar(20) not null;
alter table stagingtable
alter column year varchar(20) not null;
alter table production
alter column organisation_code varchar(20) not null;
alter table production
alter column month varchar(20) not null;
alter table production
alter column year varchar(20) not null;
go

/* create private keys */
alter table stagingtable
add constraint pk_stagingtable primary key(organisation_code,month,year)
alter table production
add constraint pk_production primary key(organisation_code,month,year)
go

/* update existing rows */
update p
set p.period=s.period
,p.organisationdesc=s.organisationdesc
,p.total=s.total
,p.status=s.status
from production as p
inner join stagingtable as s
on s.organisation_code=p.organisation_code
and s.year=p.year
and s.month=p.month
where s.period<>p.period
or s.organisationdesc<>p.organisationdesc
or isnull(s.total,0x7fffffff)<>isnull(p.total,0x7fffffff)
or isnull(s.status,'null')<>isnull(p.status,'null');

/* insert missing rows */
insert into production
select *
from stagingtable as s
where not exists (select 1
from production as p
where p.organisation_code=s.organisation_code
and p.year=s.year
and p.month=p.month
);[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources