TSQL to UPSERT from staging to prod table ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

TSQL to UPSERT from staging to prod table ??

Author

Topic

exxoid
Starting Member

5 Posts

Posted - 2010-09-01 : 00:01:55

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
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-01 : 13:06:21

what was the error it threw?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-01 : 16:19:50

For SSIS, pull data from your Source do a LookUp to the Destination on the FLDEOL column. Send the Unmached output from the Lookup to a destination (insert) and send the Matched records to an OLE DB Command to update to matched rows. That's the genreal idea. There are lots of web sites that'll have more detail on how to go about "upserting."

For SQL, here is some basic Update/Insert code that might help. There are different ways to do this too including the MERGE statement:

-- Update existing values
UPDATE
	prod
SET
	Column1 = Staging.Column1,
	Column2 = Staging.Column2,
	Column3 = Staging.Column3,
	Column4 = Staging.Column4,
	...
FROM
	dbo.production_sales AS prod
INNER JOIN
	Staging
	ON Staging.FLDEOL = prod.FLDEOL	
	
-- Insert new values	
INSERT 
	dbo.production_sales
VALUES 
	(<Column List>)
SELECT
	<Column List>
FROM
	Staging
LEFT JOIN
	dbo.production_sales
	ON Staging.FLDEOL = productin_sales.FLDEOL
WHERE
	production_sales.FLDEOL IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources