• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 716
  • Last Modified:

Need to INSERT/UPDATE from STAGING to PRODUCTION table in SQL 2005 or SSIS?

Hello,

Initially I posted here when I was testing to see if its possible to use SSIS to import data, after playing with SCD Type-2 I was able to create dummy tables and import data back and forth, however the scope is a little more complicated and SCD Type-2 does not seem to work, or I am not implementing it right.

What I need to be able to do, is from my "STAGING" table, INSERT new records where the "FLDEOL" column is not matched, if it is then UPDATE the entire record. I want FLDEOL to be my primary key, so that no two identical records can exist.

The able has just under 200 columns, and when we do the initial bulk insert, its going to contain about 4.9 million records, after that about 10,000 new and 10,000 updates per day.

I have posted the CREATE TABLE statement I use for both my STAGING and PRODUCTION table, based on that I want to update/insert records.

I have spent the last 2 days hitting my head against the keyboard because I am new to SSIS and ETL, and trying to follow online examples has not worked.

Could someone show me how I would do this in a SQL Statement or SSIS?

For now I have most (I have started to define them) of my columns as VARCHAR(255), I am going to go in and fine tune these after I get a working concept of ETL for STAGING to PRODUCTION.

I tried the below with a type 2 SCD but it barked errors at me after about 6k records, so here I :(

Any help would be appreciated and thank you for your time.
CREATE TABLE [dbo].production_sales](
       [SEQ] [int] NULL,
       [TYP] [varchar](1) NULL,
       [SALEDATE] [datetime] NULL,
       [INVOICED] [datetime] NULL,
       [WEEKDATE] [datetime] NULL,
       [EWK] [int] NULL,
       [ZWK] [int] NULL,
       [IY] [int] NULL,
       [WK] [int] NULL,
       [ROMO] [int] NULL,
       [ROYR] [int] NULL,
       [YRTD] [int] NULL,
       [MO] [int] NULL,
       [YR] [int] NULL,
       [FM] [int] NULL,
       [FY] [int] NULL,
       [PM] [int] NULL,
       [PY] [int] NULL,
       [CO] [int] NULL,
       [CMP] [varchar](3) NULL,
       [COMPANY DESCRIPTION] [varchar](50) NULL,
       [CURR] [varchar](3) NULL,
       [EXCH] [int] NULL,
       [OFFICE] [varchar](6) NULL,
       [DIV] [int] NULL,
       [OFFICE NAME] [varchar](20) NULL,
       [OC] [varchar](2) NULL,
       [BROKER] [varchar](6) NULL,
       [BROKER NAME] [varchar](30) NULL,
       [BT] [varchar](1) NULL,
       [SLSPER] [varchar](5) NULL,
       [SALES PERSON NAME] [varchar](50) NULL,
       [TF NUMBER] [varchar](10) NULL,
       [TCOMPLAIN] [varchar](50) NULL,
       [TCMP] [varchar](1) NULL,
       [ORDER NUMB] [varchar](10) NULL,
       [LOAD NUMB] [varchar](10) NULL,
       [PO NUMB] [varchar](50) NULL,
       [INVOICE] [varchar](255) NULL,
       [TRCO] [varchar](255) NULL,
       [W H] [varchar](255) NULL,
       [SHIPDATE] [varchar](255) NULL,
       [BOOKDATE] [varchar](255) NULL,
       [FRUP NUMBR] [varchar](255) NULL,
       [VENDOR] [varchar](255) NULL,
       [FRUP VENDOR NAME] [varchar](255) NULL,
       [FRUP ADDRESS] [varchar](255) NULL,
       [FRUP CITY] [varchar](255) NULL,
       [FRUP STATE] [varchar](255) NULL,
       [FRUP ZIPCO] [varchar](255) NULL,
       [FRUP COUNTRY] [varchar](255) NULL,
       [FRUP COST] [varchar](255) NULL,
       [LOC] [varchar](255) NULL,
       [SLC] [varchar](255) NULL,
       [SLR] [varchar](255) NULL,
       [SHIP LOCATION NAME] [varchar](255) NULL,
       [SHIPTO] [varchar](255) NULL,
       [SALPHA] [varchar](255) NULL,
       [SSALPH] [varchar](255) NULL,
       [SHIP TO ADDRESS] [varchar](255) NULL,
       [SHIP TO CUSTOMER NAME] [varchar](255) NULL,
       [SHIPTO CUSTOMER CITY] [varchar](255) NULL,
       [S ZIPCODE ] [varchar](255) NULL,
       [STERR] [varchar](255) NULL,
       [STYPE] [varchar](255) NULL,
       [SCOUNTRY] [varchar](255) NULL,
       [CITYCODE] [varchar](255) NULL,
       [STIER1] [varchar](255) NULL,
       [S1] [varchar](255) NULL,
       [STIER1-NAME] [varchar](255) NULL,
       [STIER2] [varchar](255) NULL,
       [S2] [varchar](255) NULL,
       [STIER3] [varchar](255) NULL,
       [S3] [varchar](255) NULL,
       [STIER4] [varchar](255) NULL,
       [S4] [varchar](255) NULL,
       [SRBM] [varchar](255) NULL,
       [SHIP TO RBM NAME] [varchar](255) NULL,
       [SHIP TO SMU NAME] [varchar](255) NULL,
       [SMUMGR] [varchar](255) NULL,
       [BILLTO] [varchar](255) NULL,
       [BALPHA] [varchar](255) NULL,
       [BILL TO CUSTOMER NAME] [varchar](255) NULL,
       [BILL TO ADDRESS] [varchar](255) NULL,
       [BILLTO CUSTOMER CITY] [varchar](255) NULL,
       [B ZIPCODE] [varchar](255) NULL,
       [BTERR] [varchar](255) NULL,
       [BTYPE] [varchar](255) NULL,
       [BCOUNTRY] [varchar](255) NULL,
       [BTIER1] [varchar](255) NULL,
       [B1] [varchar](255) NULL,
       [BTIER2] [varchar](255) NULL,
       [B2] [varchar](255) NULL,
       [BTIER3] [varchar](255) NULL,
       [B3] [varchar](255) NULL,
       [BTIER4] [varchar](255) NULL,
       [B4] [varchar](255) NULL,
       [BRBM] [varchar](255) NULL,
       [BILL TO RBM NAME] [varchar](255) NULL,
       [BILL TO SMU NAME] [varchar](255) NULL,
       [BMUMGR] [varchar](255) NULL,
       [ITEM CHRG] [varchar](255) NULL,
       [ITEM CHARGE DESCRIPTION] [varchar](255) NULL,
       [ITEM CHARGE SHORT DESCRIPTION] [varchar](255) NULL,
       [MIN] [varchar](255) NULL,
       [MAJ] [varchar](255) NULL,
       [CE EQUIV] [varchar](255) NULL,
       [TE EQUIV] [varchar](255) NULL,
       [1L EQUIV] [varchar](255) NULL,
       [RG] [varchar](255) NULL,
       [REGION NAME] [varchar](255) NULL,
       [COM] [varchar](255) NULL,
       [COMN] [varchar](255) NULL,
       [COMMODITY NAME ] [varchar](255) NULL,
       [VAR] [varchar](255) NULL,
       [VAR ALPHA] [varchar](255) NULL,
       [MKTG VAR] [varchar](255) NULL,
       [VARIETY NAME] [varchar](255) NULL,
       [VARGRP] [varchar](255) NULL,
       [FCCAT] [varchar](255) NULL,
       [SLSCT] [varchar](255) NULL,
       [PST] [varchar](255) NULL,
       [STYLE] [varchar](255) NULL,
       [PACK STYLE NAME] [varchar](255) NULL,
       [S] [varchar](255) NULL,
       [PTY] [varchar](255) NULL,
       [TYPE] [varchar](255) NULL,
       [PACK TYPE NAME] [varchar](255) NULL,
       [SIZ] [varchar](255) NULL,
       [SIZE NAME] [varchar](255) NULL,
       [LOT] [varchar](255) NULL,
       [LOT NAME] [varchar](255) NULL,
       [LBL] [varchar](255) NULL,
       [LAB] [varchar](255) NULL,
       [LABEL NAME] [varchar](255) NULL,
       [LR] [varchar](255) NULL,
       [GM] [varchar](255) NULL,
       [DEAL] [varchar](255) NULL,
       [SDEA] [varchar](255) NULL,
       [DEALNAME] [varchar](255) NULL,
       [SEA] [varchar](255) NULL,
       [SA] [varchar](255) NULL,
       [SATD] [varchar](255) NULL,
       [EXPRTR] [varchar](255) NULL,
       [EXPORTER NAME] [varchar](255) NULL,
       [EXPORTER GROUP] [varchar](255) NULL,
       [EXPORTER REGION] [varchar](255) NULL,
       [TRNSPORT] [varchar](255) NULL,
       [TRANSPORT NAME] [varchar](255) NULL,
       [ARR DATE] [varchar](255) NULL,
       [ARR] [varchar](255) NULL,
       [ALC] [varchar](255) NULL,
       [ALR] [varchar](255) NULL,
       [ARRIVAL LOCATION NAME] [varchar](255) NULL,
       [QC] [varchar](255) NULL,
       [USDA] [varchar](255) NULL,
       [GR] [varchar](255) NULL,
       [EG] [varchar](255) NULL,
       [TT] [varchar](255) NULL,
       [C] [varchar](255) NULL,
       [NET VOLUME] [varchar](255) NULL,
       [YTD VOLUME] [varchar](255) NULL,
       [SHIP VOLUME] [varchar](255) NULL,
       [CE VOLUME] [varchar](255) NULL,
       [TE VOLUME] [varchar](255) NULL,
       [1LYR VOLUME] [varchar](255) NULL,
       [2LYR VOLUME] [varchar](255) NULL,
       [QTY SHIPPED] [varchar](255) NULL,
       [QTY EQUIVALENT] [varchar](255) NULL,
       [QTY FOB] [varchar](255) NULL,
       [QTY FOB EQUIV] [varchar](255) NULL,
       [QTY DEL] [varchar](255) NULL,
       [QTY DEL EQUIV] [varchar](255) NULL,
       [O E FOB] [varchar](255) NULL,
       [O E FREIGHT] [varchar](255) NULL,
       [O E BROKERAGE] [varchar](255) NULL,
       [O E OTHER] [varchar](255) NULL,
       [T F FOB] [varchar](255) NULL,
       [NET FOB] [varchar](255) NULL,
       [GROWER FOB] [varchar](255) NULL,
       [GROSS] [varchar](255) NULL,
       [NET SALES] [varchar](255) NULL,
       [AVERAGE GROSS] [varchar](255) NULL,
       [TOTAL INCOME] [varchar](255) NULL,
       [TOTAL REVENUE] [varchar](255) NULL,
       [ACCRUED INCOME] [varchar](255) NULL,
       [FRUP ACTUAL] [varchar](255) NULL,
       [FRUP ACCRUED] [varchar](255) NULL,
       [FRUP ESTIMATE] [varchar](255) NULL,
       [BROKERAGE ONLY] [varchar](255) NULL,
       [BROKERAGE INC] [varchar](255) NULL,
       [COMMISSION] [varchar](255) NULL,
       [ADMIN FEE] [varchar](255) NULL,
       [VAN WH INCOME] [varchar](255) NULL,
       [SUB BROKERAGE] [varchar](255) NULL,
       [3RD PARTY BRK] [varchar](255) NULL,
       [REBATE] [varchar](255) NULL,
       [ADVERTISING] [varchar](255) NULL,
       [MARKET ADJ] [varchar](255) NULL,
       [QTY EQU MARKET] [varchar](255) NULL,
       [QTY MARKET] [varchar](255) NULL,
       [QUALITY ADJ] [varchar](255) NULL,
       [QTY EQU QUALTY] [varchar](255) NULL,
       [QTY QUALITY] [varchar](255) NULL,
       [RETURN ADJ] [varchar](255) NULL,
       [QTY EQU RETURN] [varchar](255) NULL,
       [QTY RETURN] [varchar](255) NULL,
       [REJECT ADJ] [varchar](255) NULL,
       [QTY EQU REJECT] [varchar](255) NULL,
       [QTY REJECT] [varchar](255) NULL,
       [ADJUST TOTAL] [varchar](255) NULL,
       [QTY EQU ADJUST] [varchar](255) NULL,
       [QTY ADJUST] [varchar](255) NULL,
       [AVERAGE ADJUST] [varchar](255) NULL,
       [QTY FORECST] [varchar](255) NULL,
       [FORECAST FOB] [varchar](255) NULL,
       [FORECST INCOME] [varchar](255) NULL,
       [FORECST REBATE] [varchar](255) NULL,
       [FORECAST ADVT] [varchar](255) NULL,
       [FORECAST ADMIN] [varchar](255) NULL,
       [FORECAST COMM] [varchar](255) NULL,
       [FORECAST SUB] [varchar](255) NULL,
       [FORECAST 3RD] [varchar](255) NULL,
       [F C MISC PLAN] [varchar](255) NULL,
       [F C MISCACTUAL] [varchar](255) NULL,
       [FLDEOL] [varchar](255) NULL
)

Open in new window

0
mirde
Asked:
mirde
  • 4
  • 3
1 Solution
 
mirdeAuthor Commented:
My initial "sample" test was using this method:

http://jahaines.blogspot.com/2009/09/sss-performing-upsert.html

Which makes use of SSIS and SQL for the UPSERT, however any help on adopting the custom view for my table?

I am new to SSIS, still going through my SQL DBA books, and got hit by the above SSIS project in the mean time.
0
 
willz123Commented:
Hi

Maybe it would be better to use SSIS or BCP to bulk insert all the records into a temp table in your production enviromnet and then write a query to insert / update between the tables

maybe somthing like



update production_sales set production_sales.field1 = temp.field1
from 
production_sales 
inner join temp on temp.FLDEOL = production_sales.FLDEOL



insert into production_sales
select * from
temp
where FLDEOL not in
(
	select temp.FLDEOL from production_sales 
	inner join temp on temp.FLDEOL = production_sales.FLDEOL
)

Open in new window

0
 
mirdeAuthor Commented:
I have already used SSIS to bulk insert all the records into a staging area, now I want to import it from staging to production table, and your example will work for this.

Is there a way I can do this in batch set? so that it takes the first 10,000 (or so) records from my staging table and pushes into production. Preferable that with some type of error handling such as inserting a DATETIME stamp into the sales table for every record?

How would I accomplish this?

Thanks for your input.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
willz123Commented:

For the datestamp you need to add GETDATE() to your select statement and make sure it corresponds to a column - so if you added another column datestamp as the last column you could do the following

select *,GETDATE()
from temp ....

For the batching you can do the following or if you want to run each batch manually just use
  Select Top 10000 * fom ...
set nocount on
SET ROWCOUNT 10000 --  Or whatever

WHILE 1 = 1
BEGIN

insert into production_sales
select * from
... 

IF @@ROWCOUNT = 0
BREAK
END

Open in new window

0
 
mirdeAuthor Commented:
Thinking about this further, if I wanted to do this with INSERT/DELETE, and not update function, how could I accomplish this?

Based ob my FLDEOL column, if the data that is coming from my staging table exists in the production table, find all the entries and delete the records, then insert new ones.

Ultimately, if the FLDEOL record exists in production delete the whole record, and add the new one, no need to update certain columns in the record based on the FLDEOL key.

How would I accomplish this?

Thanks.
0
 
willz123Commented:
Hi Mirde

You can use the following to delete records that match, then you can run the insert statment after to insert all records

if you have a lot of records to delete you may want to use the batch code above for the delete

Hope that helps you out
-- run first to delete records
DELETE from
production_sales
where FLDEOL in
(
	select temp.FLDEOL from production_sales 
	inner join temp on temp.FLDEOL = production_sales.FLDEOL
)

-- run to insert new records
insert into production_sales
select * from
temp

Open in new window

0
 
mirdeAuthor Commented:
Sorry for the delay and thank you for the answer, that worked.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now