Normalization and sales levels

When certain purchases are made, the commission rate depends on the size
of the purchase. We get data from an external source that shows the
expected commission rate, depending on the size of the purchase.
The commission rate/sales level data comes to us in an Excel file, and I
need to convert it to some appropriate SQL structure. (I don't have any
DDL yet because I haven't designed the tables.)
Here's an example of one commission level record that we get from the
external source:
ID, SalesLvl1, SalesLvl2, SalesLvl3, SalesLvl4, Rate1, Rate2, Rate3,
Rate4
1X123, 10000, 25000, 50000, 1E14, 0.05, 0.035, 0.025,
0.02
After I turn this into a sensible SQL table, then I'll see a sale of X
dollars for product ID 1X123, and I need to find the expected commission
level.
(Sales from .01 to SalesLvl1 (including = SalesLvl1) get Rate1; sales
above SalesLvl1 and less than or equal to SalesLvl2 get Rate2, etc. It
is expected that no sale will be greater than the last level.)
If I expand the incoming data into four records with the product ID, the
max sales level for that rate, and the rate, then I would be looking for
the "first" sales level that's larger than the purchase size, and then
get the rate. But that implies an ordering on the records, and I know
that SQL records don't intrinsically have an ordering.
Do I need to design each row to hold the min and the max sales level for
that rate, and then search for records where the sales level is between
that min and max? How can I prevent overlapping records?
I have searched the Web for SQL and commission, and also sales level,
and breakpoint (of course searching on SQL and breakpoint gives me
unrelated stuff), and I can't find examples. If anyone can point me to
examples of how to do this -- I'm sure it is not a new problem -- I
would be grateful.
Thanks.
David WalkerShoudl go along with this:
Select O.Quantity,O.ArticleID, SR.Rate
From Orders O
INNER JOIN
(
Select SalesRate.ArticleId,SalesRate.Rate,MAX(SalesLvl)
FROM SalesRate
Where O.Quantity > SalesRate.SalesLvl
Group by SalesRate.ArticleId,SalesRate.Rate
) SR
ON SR.ArticleID = O.ArticleID
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"DWalker" <none@.none.com> schrieb im Newsbeitrag
news:%2393p2MFQFHA.356@.TK2MSFTNGP14.phx.gbl...
> When certain purchases are made, the commission rate depends on the size
> of the purchase. We get data from an external source that shows the
> expected commission rate, depending on the size of the purchase.
> The commission rate/sales level data comes to us in an Excel file, and I
> need to convert it to some appropriate SQL structure. (I don't have any
> DDL yet because I haven't designed the tables.)
> Here's an example of one commission level record that we get from the
> external source:
> ID, SalesLvl1, SalesLvl2, SalesLvl3, SalesLvl4, Rate1, Rate2, Rate3,
> Rate4
> 1X123, 10000, 25000, 50000, 1E14, 0.05, 0.035, 0.025,
> 0.02
> After I turn this into a sensible SQL table, then I'll see a sale of X
> dollars for product ID 1X123, and I need to find the expected commission
> level.
> (Sales from .01 to SalesLvl1 (including = SalesLvl1) get Rate1; sales
> above SalesLvl1 and less than or equal to SalesLvl2 get Rate2, etc. It
> is expected that no sale will be greater than the last level.)
> If I expand the incoming data into four records with the product ID, the
> max sales level for that rate, and the rate, then I would be looking for
> the "first" sales level that's larger than the purchase size, and then
> get the rate. But that implies an ordering on the records, and I know
> that SQL records don't intrinsically have an ordering.
> Do I need to design each row to hold the min and the max sales level for
> that rate, and then search for records where the sales level is between
> that min and max? How can I prevent overlapping records?
> I have searched the Web for SQL and commission, and also sales level,
> and breakpoint (of course searching on SQL and breakpoint gives me
> unrelated stuff), and I can't find examples. If anyone can point me to
> examples of how to do this -- I'm sure it is not a new problem -- I
> would be grateful.
> Thanks.
> David Walker
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
I'd create a table like this:
CREATE TABLE SalesRates (
product_id char(5) not null ,
lvl int not null CHECK (lvl BETWEEN 1 AND 4) ,
amt_start decimal(17,2) not null , -- scale of 17 'cuz of 1E14 value
amt_end decimal(17,2) not null ,
rate decimal(5,4) not null
CHECK (rate > 0 AND rate < 1) ,
CONSTRAINT PK_SalesRates PRIMARY KEY (product_id, lvl, amt_start) ,
CONSTRAINT CK_InBounds CHECK (amt_start < amt_end AND amt_end >
amt_start)
)
go
-- and a trigger like the following to prevent overlaps:
CREATE TRIGGER NoOverlap ON SalesRates FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT = 0
RETURN
IF (SELECT Count(*)
FROM SalesRates AS R, inserted as i
WHERE i.product_id <> R.product_id
AND (i.amt_start BETWEEN R.amt_start AND R.amt_end
OR i.amt_end BETWEEN R.amt_start AND R.amt_end)) > 0
BEGIN
RAISERROR ('The Amount overlaps existing data',16,1)
ROLLBACK TRANSACTION
END
go
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQl14QYechKqOuFEgEQLkcgCgpHpzXDtfP8rR
sv/RqB8e4CoV46kAn3TA
zKORMNvGlmntLqJGTG+y18VI
=gZYn
--END PGP SIGNATURE--
DWalker wrote:
> When certain purchases are made, the commission rate depends on the size
> of the purchase. We get data from an external source that shows the
> expected commission rate, depending on the size of the purchase.
> The commission rate/sales level data comes to us in an Excel file, and I
> need to convert it to some appropriate SQL structure. (I don't have any
> DDL yet because I haven't designed the tables.)
> Here's an example of one commission level record that we get from the
> external source:
> ID, SalesLvl1, SalesLvl2, SalesLvl3, SalesLvl4, Rate1, Rate2, Rate3,
> Rate4
> 1X123, 10000, 25000, 50000, 1E14, 0.05, 0.035, 0.025,
> 0.02
> After I turn this into a sensible SQL table, then I'll see a sale of X
> dollars for product ID 1X123, and I need to find the expected commission
> level.
> (Sales from .01 to SalesLvl1 (including = SalesLvl1) get Rate1; sales
> above SalesLvl1 and less than or equal to SalesLvl2 get Rate2, etc. It
> is expected that no sale will be greater than the last level.)
> If I expand the incoming data into four records with the product ID, the
> max sales level for that rate, and the rate, then I would be looking for
> the "first" sales level that's larger than the purchase size, and then
> get the rate. But that implies an ordering on the records, and I know
> that SQL records don't intrinsically have an ordering.
> Do I need to design each row to hold the min and the max sales level for
> that rate, and then search for records where the sales level is between
> that min and max? How can I prevent overlapping records?
> I have searched the Web for SQL and commission, and also sales level,
> and breakpoint (of course searching on SQL and breakpoint gives me
> unrelated stuff), and I can't find examples. If anyone can point me to
> examples of how to do this -- I'm sure it is not a new problem -- I
> would be grateful.
> Thanks.
> David Walker
>|||Table structur should go like that (denormlized
ArticleId
SalesLvl
SalesRate
Overlapping could be controlled with a check constaint or a trigger.
Have a nice day,
HTH, Jens Smeyer
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:eAkBDaFQFHA.3144@.tk2msftngp13.phx.gbl...
> Shoudl go along with this:
>
> Select O.Quantity,O.ArticleID, SR.Rate
> From Orders O
> INNER JOIN
> (
> Select SalesRate.ArticleId,SalesRate.Rate,MAX(SalesLvl)
> FROM SalesRate
> Where O.Quantity > SalesRate.SalesLvl
> Group by SalesRate.ArticleId,SalesRate.Rate
> ) SR
> ON SR.ArticleID = O.ArticleID
>
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "DWalker" <none@.none.com> schrieb im Newsbeitrag
> news:%2393p2MFQFHA.356@.TK2MSFTNGP14.phx.gbl...
>|||Great, thanks to everyone who answered. It helps!
David
MGFoster <me@.privacy.com> wrote in
news:iMe7e.5726$lP1.2917@.newsread1.news.pas.earthlink.net:

> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
>
> I'd create a table like this:
> CREATE TABLE SalesRates (
> product_id char(5) not null ,
> lvl int not null CHECK (lvl BETWEEN 1 AND 4) ,
> amt_start decimal(17,2) not null , -- scale of 17 'cuz of 1E14
> value amt_end decimal(17,2) not null ,
> rate decimal(5,4) not null
> CHECK (rate > 0 AND rate < 1) ,
> CONSTRAINT PK_SalesRates PRIMARY KEY (product_id, lvl, amt_start) ,
> CONSTRAINT CK_InBounds CHECK (amt_start < amt_end AND amt_end >
> amt_start)
> )
> go
> -- and a trigger like the following to prevent overlaps:
> CREATE TRIGGER NoOverlap ON SalesRates FOR INSERT, UPDATE
> AS
> IF @.@.ROWCOUNT = 0
> RETURN
> IF (SELECT Count(*)
> FROM SalesRates AS R, inserted as i
> WHERE i.product_id <> R.product_id
> AND (i.amt_start BETWEEN R.amt_start AND R.amt_end
> OR i.amt_end BETWEEN R.amt_start AND R.amt_end)) > 0
> BEGIN
> RAISERROR ('The Amount overlaps existing data',16,1)
> ROLLBACK TRANSACTION
> END
> go|||Thanks, Jens.
David
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
in news:OyD7COGQFHA.1340@.TK2MSFTNGP10.phx.gbl:

> Table structur should go like that (denormlized
> ArticleId
> SalesLvl
> SalesRate
> Overlapping could be controlled with a check constaint or a trigger.
> Have a nice day,
> HTH, Jens Smeyer
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> schrieb im Newsbeitrag news:eAkBDaFQFHA.3144@.tk2msftngp13.phx.gbl...
>

noob question of how to combine two tables from two different servers.

I got two tables from 2 different server A and B.

I do a OLEDB source [server A] with "select ID, currencyNo, exchangerate from table A"

I do another OLEDB [ server B] source with "select currencyNo, currencyName from table B"

i want to combine these two OLEDB sources with a resultset

"select ID, currencyNo, currencyName, exchangerate from table A , B

where A.currencyNo = B.CurrencyNo"

how do i do this in SSIS? sorry if i m a noob. I dun want to use linked servers. can someone help?

Running that kind of query is not something that SSIS is going to do magically for you. You have to ask your DBA to set link servers (assuming they are SQL Server).

Other option is to use 2 OLE DB source and then use a Merge Join transformation to merge both sources in a single output.

Both solutions may impact negatively the performance; but that will be really an issue depending in other factors like volume of data and hardware configuration.

A third option is to break work in 2 pieces; first extract data from each table and load it in a common database and then once both tables are in the same database you can use a single ole db source component.

As you can see you have several options

Rafael Salas

|||Just remember that if using a Merge Join transformation, your inputs need to be sorted on the key. Also, it is best to do the sort in your source SQL if possible, and avoid using the sort transformation in SSIS.

Phil|||

i got problems using the merge join....it say i need to sort the data....

|||That's what I said you needed to do in my previous post.|||Thanks, i solved my problem!!

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
DonUse select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:

> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don

>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
message[vbcol=seagreen]
90[vbcol=seagreen]
structure[vbcol=seagreen]
>
>.
>|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don

>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
>
90[vbcol=seagreen]
>.
>|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
>
> operation in SQL Server.
> by doing a Select
> set. This still
> compared to the fully
> need to be in the
> message
> 90
> structure|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:

> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
>
> N'
> N'
> maybe 10 million
> queryout on an indexed
> 90
>

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
DonUse select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with 90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file structure
>> address, char,30
>> thanks,
>> Don
>|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don
>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
>> Use select into statement
>> That is not logged.
>> You can select * or select fieldlist.
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file
structure
>> address, char,30
>> thanks,
>> Don
>>
>
>.
>|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don
>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file structure
>> address, char,30
>> thanks,
>> Don
>>
>.
>|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
>>--Original Message--
>>Just to be clear you can never have a non-logged
> operation in SQL Server.
>>You can get what is called a Minimally Logged operation
> by doing a Select
>>Into, BCP or Bulk Insert when the proper conditions are
> set. This still
>>logs entries to the transaction log but are minimal
> compared to the fully
>>logged operations. In the case of the Select Into you
> need to be in the
>>Bulk Logged or Simple recovery mode for this to occur.
>>
>>--
>>Andrew J. Kelly SQL MVP
>>
>><news.optonline.net> wrote in message
>>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
>> Use select into statement
>> That is not logged.
>> You can select * or select fieldlist.
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>> SQL 7.0
>> I need to use nonlogged bcp to copy from a table with
> 90
>> millin records to an empty table structure that's
>> different than the source structure.
>> Does someone know the syntax?
>> old table example:
>> name, char, 30
>> address,char,30
>> new table example:
>> name,char,30
>> id,decimal,10 this is NULL on the source file
> structure
>> address, char,30
>> thanks,
>> Don
>>
>>
>>.|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:
> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
> >--Original Message--
> >To do this I would create a view on the new table
> >create view vw_newtable
> >as
> >select * from newtable
> >
> >than bcp the data out
> >
> >master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
> N'
> >then bcp in
> >master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
> N'
> >
> >Note there must be no indexes on the dest table.
> >For 90 million recs I would consider doing the above but
> maybe 10 million
> >recs at a time. Use a query to extract the data with
> queryout on an indexed
> >column (or you can use views in the same way)
> >
> >
> >
> >
> >
> >
> >"Don" wrote:
> >
> >> SQL 7.0
> >>
> >> I need to use nonlogged bcp to copy from a table with
> 90
> >> millin records to an empty table structure that's
> >> different than the source structure.
> >> Does someone know the syntax?
> >>
> >> old table example:
> >> name, char, 30
> >> address,char,30
> >>
> >> new table example:
> >> name,char,30
> >> id,decimal,10 this is NULL on the source file structure
> >> address, char,30
> >>
> >> thanks,
> >> Don
> >>
> >>
> >.
> >
>sql

NonLogged Bulk copy syntax

SQL 7.0
I need to use nonlogged bcp to copy from a table with 90
millin records to an empty table structure that's
different than the source structure.
Does someone know the syntax?
old table example:
name, char, 30
address,char,30
new table example:
name,char,30
id,decimal,10 this is NULL on the source file structure
address, char,30
thanks,
Don
Use select into statement
That is not logged.
You can select * or select fieldlist.
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>
|||Just to be clear you can never have a non-logged operation in SQL Server.
You can get what is called a Minimally Logged operation by doing a Select
Into, BCP or Bulk Insert when the proper conditions are set. This still
logs entries to the transaction log but are minimal compared to the fully
logged operations. In the case of the Select Into you need to be in the
Bulk Logged or Simple recovery mode for this to occur.
Andrew J. Kelly SQL MVP
<news.optonline.net> wrote in message
news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
> Use select into statement
> That is not logged.
> You can select * or select fieldlist.
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:54aa01c4c911$c07226b0$a401280a@.phx.gbl...
>
|||To do this I would create a view on the new table
create view vw_newtable
as
select * from newtable
than bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -N'
Note there must be no indexes on the dest table.
For 90 million recs I would consider doing the above but maybe 10 million
recs at a time. Use a query to extract the data with queryout on an indexed
column (or you can use views in the same way)
"Don" wrote:

> SQL 7.0
> I need to use nonlogged bcp to copy from a table with 90
> millin records to an empty table structure that's
> different than the source structure.
> Does someone know the syntax?
> old table example:
> name, char, 30
> address,char,30
> new table example:
> name,char,30
> id,decimal,10 this is NULL on the source file structure
> address, char,30
> thanks,
> Don
>
|||I'm using SQL 7.0 and the modes you mention are for SQL
2000.
Don

>--Original Message--
>Just to be clear you can never have a non-logged
operation in SQL Server.
>You can get what is called a Minimally Logged operation
by doing a Select
>Into, BCP or Bulk Insert when the proper conditions are
set. This still
>logs entries to the transaction log but are minimal
compared to the fully
>logged operations. In the case of the Select Into you
need to be in the[vbcol=seagreen]
>Bulk Logged or Simple recovery mode for this to occur.
>
>--
>Andrew J. Kelly SQL MVP
>
><news.optonline.net> wrote in message
>news:%235btXEVyEHA.3784@.tk2msftngp13.phx.gbl...
message[vbcol=seagreen]
90[vbcol=seagreen]
structure
>
>.
>
|||I guess I don't follow. for one thing you say bcp in, but
then the command is out.
then i don't see where anything done in the view is used
again in the bcp commands.
thanks,
don

>--Original Message--
>To do this I would create a view on the new table
>create view vw_newtable
>as
>select * from newtable
>than bcp the data out
>master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -
N'
>then bcp in
>master..xp_cmdshell 'bcp mydb..newtbl out c:\newtbl.txt -
N'
>Note there must be no indexes on the dest table.
>For 90 million recs I would consider doing the above but
maybe 10 million
>recs at a time. Use a query to extract the data with
queryout on an indexed[vbcol=seagreen]
>column (or you can use views in the same way)
>
>
>
>"Don" wrote:
90
>.
>
|||Then make sure you set the Select Into / Bulk Insert mode and it will be the
same.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:07db01c4c9bb$f22588f0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'm using SQL 7.0 and the modes you mention are for SQL
> 2000.
> Don
> operation in SQL Server.
> by doing a Select
> set. This still
> compared to the fully
> need to be in the
> message
> 90
> structure
|||doh (deliberate mistake - left to the reader)
create view vw_newtable
as
select * from newtable
then bcp the data out
master..xp_cmdshell 'bcp mydb..oldtbl out c:\oldtbl.txt -N'
then bcp in
master..xp_cmdshell 'bcp mydb..vw_newtbl in c:\newtbl.txt -N'
"Don" wrote:

> I guess I don't follow. for one thing you say bcp in, but
> then the command is out.
> then i don't see where anything done in the view is used
> again in the bcp commands.
> thanks,
> don
> N'
> N'
> maybe 10 million
> queryout on an indexed
> 90
>