Solved

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

Posted on 2010-08-31
7
679 Views
Last Modified: 2012-05-10
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
Comment
Question by:mirde
  • 4
  • 3
7 Comments
 

Author Comment

by:mirde
Comment Utility
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
 
LVL 2

Expert Comment

by:willz123
Comment Utility
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
 

Author Comment

by:mirde
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Expert Comment

by:willz123
Comment Utility

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
 

Author Comment

by:mirde
Comment Utility
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
 
LVL 2

Accepted Solution

by:
willz123 earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:mirde
Comment Utility
Sorry for the delay and thank you for the answer, that worked.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now