Showing posts with label orders. Show all posts
Showing posts with label orders. Show all posts

Wednesday, March 21, 2012

export (from mdb)-> import (to MS Sql server)

I have big problem.

In database MyDB.mdb I have table for example: ORDERS with colums:
- Id int
- Name varchar(20)

Also I create same table in SQL Server 2k.
I can manualy export this data from mdb database to SQL Server. But I want
to make this more automatic.

Is is possible? To import automaticaly data from mdb format to table in SQL
Server 2000?

please, help me :)

best regards
Piotr Leib

ps.
sorry for my terrible english language"BUSHII" <piotrl@.robcom.com.pl> wrote in message news:<cd3vki$6pk$1@.nemesis.news.tpi.pl>...
> I have big problem.
> In database MyDB.mdb I have table for example: ORDERS with colums:
> - Id int
> - Name varchar(20)
> Also I create same table in SQL Server 2k.
> I can manualy export this data from mdb database to SQL Server. But I want
> to make this more automatic.
> Is is possible? To import automaticaly data from mdb format to table in SQL
> Server 2000?
> please, help me :)
> best regards
> Piotr Leib
> ps.
> sorry for my terrible english language

Have you looked at DTS? It can move data directly between different
data sources. To get started quickly, you can use the "Import and
Export Data" option in the start menu. Another possibility is to
create a linked server from MSSQL to the Access DB, and then simply
INSERT...SELECT... directly - see "Linked Servers" in Books Online.
Finally, if you can export to a flat file from Access, then you can
import that file with bcp.exe or DTS.

Simon|||I just create an ODBC connection to this database server and MS Access can
export directly to ODBC. It even will create the tables for you.

Oscar...

"BUSHII" <piotrl@.robcom.com.pl> wrote in message
news:cd3vki$6pk$1@.nemesis.news.tpi.pl...
> I have big problem.
> In database MyDB.mdb I have table for example: ORDERS with colums:
> - Id int
> - Name varchar(20)
> Also I create same table in SQL Server 2k.
> I can manualy export this data from mdb database to SQL Server. But I want
> to make this more automatic.
> Is is possible? To import automaticaly data from mdb format to table in
SQL
> Server 2000?
> please, help me :)
> best regards
> Piotr Leib
> ps.
> sorry for my terrible english language

Sunday, February 26, 2012

Expand a querry for more function

I have an automated shipping software that reaches into our database and gets customer orders and processes them for UPS tags. The problem I am having is we have to manually break out line items. What I mean iss this, if there is multiples of the same thing on the order the UPS software shows a total wieght for all simular items as if it was one item.

What I am trying to do is to pre process the Order table into individuale lines so the automated feature of my UPS shipping software works.

The table looks something like this,

Odr_no, Item_no, Item_des, QTY, Unit_wght, Tot_wght
-----------------------
1200, AB123, Some stuff, 1, 10, 10
1200, AB324, More Stuff, 3, 12, 36
1300, XY32, Junk, 2, 40, 80

What I need to turn it into is"

Odr_no, Item_no, Item_des, QTY, Unit_wght, Tot_wght
-----------------------
1200, AB123, Some stuff, 1, 10, 10
1200, AB324, More Stuff, 1, 12, 36
1200, AB324, More Stuff, 1, 12, 36
1200, AB324, More Stuff, 1, 12, 36
1300, XY32, Junk, 1, 40, 80
1300, XY32, Junk, 1, 40, 80

it would also be nice to add a column to the table to track the total number of packages for each order number, kind of a 1 of 3, 2 of 3 type thing, but that is much easyier than the breack out

any help woulf be greatly apreciated

KellyCan you post the DDL of the table? There's got to me more to it...

Because you do you get..

1200, AB324, More Stuff, 1, 12, 36
1200, AB324, More Stuff, 1, 12, 36
1200, AB324, More Stuff, 1, 12, 36

From this?

1200, AB324, More Stuff, 1, 12, 36

Doesn't make sense, unless there are other things on the row...

Script the table and post the DDL like..

CREATE TABLE myTable99 (Col1 int, ect...
GO

And sample Data like..

INSERT INTO myTable99 (Col1, col2, ect)
SELECT sample data UNION ALL
SELECT sample data UNION ALL
SELECT sample data UNION ALL
SELECT sample data

and it'll be easier for us to help..|||There is a lot of extra columns we do not even use as this is a canned ERP package this is from , we are a simple manufacturing company and do not require many of the features that this package provides, I have posted two create statements, first with only the columns that we use, and second with every thing.

the columns we are interested in are,

CREATE TABLE [OEORDLIN_SQL] (
[ord_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_seq_no] [smallint] NOT NULL ,
[item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[loc] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[item_desc_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_desc_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty_ordered] [decimal](13, 4) NULL ,
[qty_to_ship] [decimal](13, 4) NULL ,
[unit_price] [decimal](13, 6) NULL ,
[uom] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[unit_weight] [decimal](13, 6) NULL ,
[tot_qty_ordered] [decimal](13, 4) NULL ,
[cus_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

The entire table is below,

CREATE TABLE [OEORDLIN_SQL] (
[ord_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_seq_no] [smallint] NOT NULL ,
[item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[item_filler] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[loc] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[pick_seq] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cus_item_no] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_desc_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_desc_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qty_ordered] [decimal](13, 4) NULL ,
[qty_to_ship] [decimal](13, 4) NULL ,
[unit_price] [decimal](13, 6) NULL ,
[discount_pct] [decimal](5, 2) NULL ,
[request_dt] [int] NOT NULL ,
[qty_bkord] [decimal](13, 4) NULL ,
[qty_return_to_stk] [decimal](13, 4) NULL ,
[bkord_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uom] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uom_ratio] [decimal](9, 5) NULL ,
[unit_cost] [decimal](13, 6) NULL ,
[unit_weight] [decimal](13, 6) NULL ,
[comm_calc_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comm_pct_or_amt] [decimal](7, 2) NULL ,
[promise_dt] [int] NOT NULL ,
[tax_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stocked_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[controlled_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[select_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tot_qty_ordered] [decimal](13, 4) NULL ,
[tot_qty_shipped] [decimal](13, 4) NULL ,
[tax_fg_1] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tax_fg_2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tax_fg_3] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[orig_price] [decimal](13, 6) NULL ,
[copy_to_bm_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[explode_kit] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[allocate_dt] [int] NULL ,
[last_post_dt] [int] NULL ,
[post_to_inv_qty] [decimal](13, 4) NULL ,
[posted_to_inv] [decimal](13, 4) NULL ,
[tot_qty_posted] [decimal](13, 4) NULL ,
[qty_allocated] [decimal](13, 4) NULL ,
[components_alloc] [decimal](13, 4) NULL ,
[bin_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cost_meth] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ser_lot_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mult_ftr_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[line_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prod_cat] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[end_item_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[reason_cd] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[feature_return] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rec_inspection] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ship_from_stk] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mult_release] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[req_ship_dt] [int] NULL ,
[qty_from_stk] [decimal](13, 4) NULL ,
[user_def_fld_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_3] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_4] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_5] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[picked_dt] [int] NULL ,
[shipped_dt] [int] NULL ,
[billed_dt] [int] NULL ,
[update_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[prc_cd_orig_price] [decimal](13, 6) NULL ,
[tax_sched] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cus_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tax_amt] [decimal](14, 2) NULL ,
[qty_bkord_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_no] [smallint] NOT NULL ,
[mfg_method] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[forced_demand] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[conf_pick_dt] [int] NULL ,
[item_release_no] [int] NULL ,
[bin_ser_lot_comp] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[offset_used_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ecs_space] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sfc_order_status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[total_cost] [decimal](14, 2) NULL ,
[po_ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rma_line_seq_no] [smallint] NULL ,
[vend_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[filler_0004] [char] (71) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO|||Here is an insert script with some data from our test database, I have only included the data we use, most of the rest is zeros or nulls,

Insert Into [OEORDLIN_SQL]
(ord_type,ord_no,line_seq_no,item_no,loc,item_desc _1,item_desc_2,qty_ordered,
qty_to_ship,unit_price,uom,unit_weight,tot_qty_ord ered,cus_no,A4GLIdentity)

Values (B,00000002,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,3.0000,.0000,64.420000,EA,26.000000,3.0000, SCHFNE,18301)
(B,00000003,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,5.0000,.0000,128.840000, EA,46.000000,6.0000,PYEFAR,20247)
(O,00056941,1,FPFF11CS,WH,2 PK 11 LB FARRIERS FORMULA, NULL,1.0000,1.0000,46.160000,EA,26.000000,1.0000,M ASCAL,34818)
(O,00056941,2,FPFF22CS,WH,2 PK 22 LB FARRIERS FORMULA, NULL,1.0000,1.0000,92.320000,EA,49.000000,1.0000,M ASCAL,34819)
(O,00056941,3,FPFF44,WH,Farrier's Formula 44 lb,NULL,5.0000,5.0000,88.220000,EA,49.000000,5.000 0,MASCAL,34820)
(O,00056942,1,FPFF44,WH,Farrier's Formula 44 lb,NULL,1.0000,1.0000,88.220000,EA,49.000000,1.000 0,RAIEGA,34821)
(O,00056943,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,87.720000,EA,46.000000,1.0000 ,CORVGA,34823)
(O,00056944,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,64.420000,EA,23.000000,1.0000 ,MAR6TX,34824)
(O,00056945,1,FPFF22CS,WH,2 PK 22 LB FARRIERS FORMULA, NULL,3.0000,3.0000,92.320000,EA,49.000000,3.0000,C TGFAL,34825)
(O,00056945,2,FPFF44,WH,Farrier's Formula 44 lb,NULL,2.0000,2.0000,88.220000,EA,49.000000,2.000 0,CTGFAL,34826)
(O,00056946,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,64.420000,EA,23.000000,1.0000 ,WYLKAL,34827)
(O,00056946,2,FPHD8TT,WH,Disenfectant 8, Tea Tree Oil,1.0000,1.0000,12.000000,EA,.000000,1.0000,WYLK AL,34828)
(O,00056947,1,FPFF44,WH,Farrier's Formula 44 lb,NULL,1.0000,1.0000,132.750000, EA,49.000000,1.0000,ZICBFL,34829)
(O,00056948,1,FPFFR2,WH,2 PK 11 LB FARRIERS FORMULA, REFILL,1.0000,1.0000,43.860000, EA,23.000000,1.0000,YATRAL,348300
(O,00056949,1,FPFF44,WH,Farrier's Formula 44 lb,NULL, 1.0000,1.0000,132.750000, EA,49.000000,1.0000,BERAIL,348310
(O,00056950,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,10.0000,10.0000,87.720000,EA,46.000000,10.0 000,MODFCA,348320
(O,00056951,1,FPFF11CS,WH,2 PK 11 LB FARRIERS FORMULA, NULL,2.0000,2.0000,46.160000,EA,26.000000,2.0000,S TAAOR,348330
(O,00056952,1,FPFFR4,WH,4 PK 11 LB FARRIERS FORMULA, REFILL,3.0000,3.0000,87.720000,EA,46.000000,3.0000 ,EMCPNY,348340
(O,00056952,2,FPFF22CS,WH,2 PK 22 LB FARRIERS FORMULA, NULL,2.0000,2.0000,92.320000,EA,49.000000,2.0000,E MCPNY,348350
(O,00056952,3,FPFF44,WH,Farrier's Formula 44 lb,NULL,4.0000,4.0000,88.220000,EA,49.000000,4.000 0,EMCPNY,34836)
(O,00056953,1,FPFF44,WH,Farrier's Formula 44 lb,NULL,1.0000,1.0000,132.750000, EA,49.000000,1.0000,BARBNY,34837)
(O,00056955,1,FPFF44,HSP, Farrier's Formula 44 lb,NULL,2.0000,2.0000,88.220000,EA,49.000000,2.000 0,CASAVT,34839)
(O,00056956,1,FPFF11CS,HSP, 2 PK 11 LB FARRIERS FORMULA, NULL, 2.0000,2.0000,46.160000,EA,26.000000,2.0000,HORNMA , 34840)
(O,00056956,2,FPFF22,HSP, Farrier's Formula 22 lb,NULL,5.0000,5.0000,46.160000,EA,25.000000,5.000 0,HORNMA,34841)
(O,00056956,3,FPFF44,HSP, Farrier's Formula 44 lb,NULL,1.0000,1.0000,88.220000,EA,49.000000,1.000 0,HORNMA,34842)
Go