consolidating two data files into one.

Using SS2000. We have a database that has two data files of the same name but
in different folders. I want to move the database to a different drive. Is
there a way to combine the two data files into one?
Thanks,
Dan D.
Kick people out of the database
do a dbcc shrinkfile emptyfile on the one going away ( that moves data into
the other file)
then alter database prod drop file file2
Backup everything first.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:6A3DC97A-CBA1-496D-A1D7-2E7CB0B39837@.microsoft.com...
> Using SS2000. We have a database that has two data files of the same name
but
> in different folders. I want to move the database to a different drive. Is
> there a way to combine the two data files into one?
> Thanks,
> --
> Dan D.
|||Thanks Wayne.
"Wayne Snyder" wrote:

> Kick people out of the database
> do a dbcc shrinkfile emptyfile on the one going away ( that moves data into
> the other file)
> then alter database prod drop file file2
> Backup everything first.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:6A3DC97A-CBA1-496D-A1D7-2E7CB0B39837@.microsoft.com...
> but
>
>
|||No problem... Have fun and good luck!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1BDAB943-A281-421C-8629-1933E12369E6@.microsoft.com...[vbcol=seagreen]
> Thanks Wayne.
> "Wayne Snyder" wrote:
into[vbcol=seagreen]
name[vbcol=seagreen]
drive. Is[vbcol=seagreen]

consolidating two data files into one.

Using SS2000. We have a database that has two data files of the same name bu
t
in different folders. I want to move the database to a different drive. Is
there a way to combine the two data files into one?
Thanks,
--
Dan D.Kick people out of the database
do a dbcc shrinkfile emptyfile on the one going away ( that moves data into
the other file)
then alter database prod drop file file2
Backup everything first.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:6A3DC97A-CBA1-496D-A1D7-2E7CB0B39837@.microsoft.com...
> Using SS2000. We have a database that has two data files of the same name
but
> in different folders. I want to move the database to a different drive. Is
> there a way to combine the two data files into one?
> Thanks,
> --
> Dan D.|||Thanks Wayne.
"Wayne Snyder" wrote:

> Kick people out of the database
> do a dbcc shrinkfile emptyfile on the one going away ( that moves data int
o
> the other file)
> then alter database prod drop file file2
> Backup everything first.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:6A3DC97A-CBA1-496D-A1D7-2E7CB0B39837@.microsoft.com...
> but
>
>|||No problem... Have fun and good luck!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1BDAB943-A281-421C-8629-1933E12369E6@.microsoft.com...[vbcol=seagreen]
> Thanks Wayne.
> "Wayne Snyder" wrote:
>
into[vbcol=seagreen]
name[vbcol=seagreen]
drive. Is[vbcol=seagreen]

Consolidating Rows and Summing Totals

I would like to know if there is an easy way to consolidate lines of the same item but differing quantities.

For example:

ITEM DESCRIPT MFR QTY
-- ---- -- --
ABC TABLE OSH 1
ABC TABLE OSH 3
ABC TABLE OSH 2
ABD CHAIR KMT 2
ABD CHAIR KMT 1
ABE PILLOW SOF 1

I would like to display this information as:

ITEM DESCRIPT MFR QTY
-- ---- -- --
ABC TABLE OSH 6
ABD CHAIR KMT 3
ABE PILLOW SOF 1

Summary: I want to consolidate those items which appear in the table more than once by combining their quantities and leaving one row that has the sum of all.

Your help is greatly appreciated.
TechRickselect ITEM, DESCRIPT, MFR
, sum(QTY)
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR

delete from yourtable

insert into yourtable
select * from temptable

rudy|||Thanks Rudy,

I just tried it but I lose my column header when I use the SUM.
Can I select by column number?

This is the error I recieved:
Server: Msg 8155, Level 16, State 1, Line 70
No column was specified for column 4 of 'temptable'

Thanks again,
TechRick|||sorry, i should have anticipated that

select ITEM, DESCRIPT, MFR
, sum(QTY) as QTY
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR|||Originally posted by r937
sorry, i should have anticipated that

select ITEM, DESCRIPT, MFR
, sum(QTY) as QTY
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR

Rudy,

I've tried repeatedly and in various ways to implement your suggestion and for some reason the rows are not being consolidated. The same information is in both tables.

Is is possible that I need to do a loop and then consolidate like items?

TechRick|||pick a couple of items, let's call them 'itm1' and 'itm2', and run a detail report

select ITEM, DESCRIPT, MFR, QTY
from yourtable
where ITEM in ('itm1','itm2')
order by ITEM, DESCRIPT, MFR

post the results (assuming only a few lines, eh)

now run a summary report

select ITEM, DESCRIPT, MFR, sum(QTY) as QTY
from yourtable
where ITEM in ('itm1','itm2')
group by ITEM, DESCRIPT, MFR

and post the results|||Originally posted by r937
pick a couple of items, let's call them 'itm1' and 'itm2', and run a detail report

select ITEM, DESCRIPT, MFR, QTY
from yourtable
where ITEM in ('itm1','itm2')
order by ITEM, DESCRIPT, MFR

post the results (assuming only a few lines, eh)

now run a summary report

select ITEM, DESCRIPT, MFR, sum(QTY) as QTY
from yourtable
where ITEM in ('itm1','itm2')
group by ITEM, DESCRIPT, MFR

and post the results

Here's what I did. It seems to work.

CODE:
select mfr_sku, title, MFR_name, Q_stk
from A1_TEMPTABLE
where mfr_sku = 'st19171wc'
order by mfr_sku, title, MFR_name

select mfr_sku, title, MFR_name, sum(Q_stk) as Q_stk
from A1_TEMPTABLE
where mfr_sku = 'item1'
group by mfr_sku, title, MFR_name

RESULTS:

ITEM1 9GB 3.5 80PIN SCSI SEA 486
ITEM1 9GB 3.5 80PIN SCSI SEA 431

ITEM1 9GB 3.5 80PIN SCSI SEA 941

I think I might know what I was doing wrong but I'll have to check it to make sure...

Thanks again,
Rick|||Originally posted by r937
sorry, i should have anticipated that

select ITEM, DESCRIPT, MFR
, sum(QTY) as QTY
into newtable
from yourtable
group by ITEM, DESCRIPT, MFR

Sure enough, it was as I thought. I was adding the QTY in the 'group by' statement and that seems to have been causing the problem. I thought that it was required but it wasn't. It's all worked out now -- working like a charm.

Thanks Rudy!

Best Regards,
TechRick

Consolidating Records

Let's say I have two tables:

CREATE TABLE dbo.OldTable
(
OldID int NOT NULL,
OldNote varchar(100) NULL
) ON [PRIMARY]
GO

AND

CREATE TABLE dbo.NewTable
(
NewID int NOT NULL IDENTITY (1, 1),
OldID int NULL,
ComboNote varchar(255) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.NewTable ADD CONSTRAINT
PK_NewTable PRIMARY KEY CLUSTERED
(
NewID
) ON [PRIMARY]

GO

OldTable's data looks like this:

OldID OldNote
-- ---
1 aaa
2 bbb
3 ccc
2 ddd
4 eee

NewTable's data (which is derived from the OldTable) should look like
this:

NewID OldID ComboNote
-- -- ---
1 1 aaa
2 2 bbb + char(13) + ddd
3 3 ccc
4 4 ddd

How can I combine the notes from OldTable where two (or more) records
have the same OldID into the NewTable's ComboNote?Something like this (untested)

select o1.OldID,o1.OldNote + char(13) + coalesce(o2.OldNote) as
ComboNote from OldTable o1
left join OldTable o2 on o1.OldID =o2.OldID
and o1.OldNote <> o2.OldNote

http://sqlservercode.blogspot.com/|||imani_technology_spam@.yahoo.com wrote:
> Let's say I have two tables:
> CREATE TABLE dbo.OldTable
> (
> OldID int NOT NULL,
> OldNote varchar(100) NULL
> ) ON [PRIMARY]
> GO
>
> AND
> CREATE TABLE dbo.NewTable
> (
> NewID int NOT NULL IDENTITY (1, 1),
> OldID int NULL,
> ComboNote varchar(255) NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE dbo.NewTable ADD CONSTRAINT
> PK_NewTable PRIMARY KEY CLUSTERED
> (
> NewID
> ) ON [PRIMARY]
> GO
> OldTable's data looks like this:
> OldID OldNote
> -- ---
> 1 aaa
> 2 bbb
> 3 ccc
> 2 ddd
> 4 eee
>
> NewTable's data (which is derived from the OldTable) should look like
> this:
> NewID OldID ComboNote
> -- -- ---
> 1 1 aaa
> 2 2 bbb + char(13) + ddd
> 3 3 ccc
> 4 4 ddd
> How can I combine the notes from OldTable where two (or more) records
> have the same OldID into the NewTable's ComboNote?

You could look at a crosstab query, but if the number of old rows for
each new row is unknown, then it's quite awkward to do in pure TSQL. A
cursor might be the best server-side solution, although using a
client-side script may be easier.

But storing multiple values in a single column is usually bad design,
and it's often difficult to query columns like that efficiently. Perhaps
you should consider generating and formatting ComboNote in the front end
when you retrieve it, rather than storing it in the database, but
obviously I don't know your environment and application, so you may have
a good reason for keeping it as a single column.

Simon|||I agree with you. Unfortunately, that is what the clients want and I
don't think they can be talked out of it.

Simon Hayes wrote:
> imani_technology_spam@.yahoo.com wrote:
> > Let's say I have two tables:
> > CREATE TABLE dbo.OldTable
> > (
> > OldID int NOT NULL,
> > OldNote varchar(100) NULL
> > ) ON [PRIMARY]
> > GO
> > AND
> > CREATE TABLE dbo.NewTable
> > (
> > NewID int NOT NULL IDENTITY (1, 1),
> > OldID int NULL,
> > ComboNote varchar(255) NULL
> > ) ON [PRIMARY]
> > GO
> > ALTER TABLE dbo.NewTable ADD CONSTRAINT
> > PK_NewTable PRIMARY KEY CLUSTERED
> > (
> > NewID
> > ) ON [PRIMARY]
> > GO
> > OldTable's data looks like this:
> > OldID OldNote
> > -- ---
> > 1 aaa
> > 2 bbb
> > 3 ccc
> > 2 ddd
> > 4 eee
> > NewTable's data (which is derived from the OldTable) should look like
> > this:
> > NewID OldID ComboNote
> > -- -- ---
> > 1 1 aaa
> > 2 2 bbb + char(13) + ddd
> > 3 3 ccc
> > 4 4 ddd
> > How can I combine the notes from OldTable where two (or more) records
> > have the same OldID into the NewTable's ComboNote?
> You could look at a crosstab query, but if the number of old rows for
> each new row is unknown, then it's quite awkward to do in pure TSQL. A
> cursor might be the best server-side solution, although using a
> client-side script may be easier.
> But storing multiple values in a single column is usually bad design,
> and it's often difficult to query columns like that efficiently. Perhaps
> you should consider generating and formatting ComboNote in the front end
> when you retrieve it, rather than storing it in the database, but
> obviously I don't know your environment and application, so you may have
> a good reason for keeping it as a single column.
> Simon|||In that case you can write a while loop or a cursor
Is this a one time thing?

http://sqlservercode.blogspot.com/|||That's helpful, but what if there are more than two records that have
the same OldID that need to go into the NewTable's ComboNote?|||Yes, this should be a one-time thing. We are doing this to migrate
some data.

I think I'll take your advice and look into cursors, although I was
taught that cursors are the work of the devil.

SQL wrote:
> In that case you can write a while loop or a cursor
> Is this a one time thing?
> http://sqlservercode.blogspot.com/

Consolidating Profiler, DTA into Mgmt Studio

Would it be possible to create "light verisons" of these external tools and integrate them into Mgmt Studio as another window in the IDE? Maybe have a "Quick Trace" window etc...

Thanks,

Derek

It is something that has been discussed yes, pelase use the product feedback centre to log it or vote for a similar one.|||

Product Suggestion Link...

http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=b8057e3f-808f-4d05-a8b9-c1b830cfd648

Consolidating multiple lookups

In many of my packages I have to translate an organizational code into a surrogate key. The method for translating is rather convoluted and involves a few lookup tables. (For example, lookup in the OrgKey table. If there is a match, use that key; if not, do a lookup of the first 5 characters in the BUKey table. If there is a match, use that key; if not, do a lookup of the first 2 characters... You get the idea.)

Since many of my packages use this same logic, I would like to consolidate it all into one custom transformation. I assume I can do this with a script transform, but then I'd lose all the caching built into the lookup transforms.

Should I just bite the bullet, and copy and paste the whole Rube Goldberg contraption of cascading lookup transforms into each package? Or is there a better solution I'm overlooking?The bullet to bite is to use sub-packages more frequently.

If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)

A second unit of re-use is a custom transform (not a script transform). THe script transform has "cut and paste" inheritence, which is no inheritance at all, so a change to one implementation forks it from the original, or both from both. That kind of re-use does not sounds as relevant to your scenario.

Nevertheless, I tend to the look at script transformations (for the most part) as proof of concept for custom transforms. If and once they are converted over to Custom transforms, then there is no more cut-and-paste inheritence (i.e. you can fix bugs once, not N times) Without taking advantage of the two re-usable objects (packages and custom components), forking will likely occur.|||

jaegd wrote:

The bullet to bite is to use sub-packages more frequently.

If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)

Maybe I'm missing something here. Since the source and destination deal with physical files only, are we talking about writing and reading to temp flat files or something? There isn't a direct way to send a data from a package to a subpackage is there?

That said, it sounds like a custom transform is probably the way to go.|||

Hubajube wrote:

jaegd wrote:

The bullet to bite is to use sub-packages more frequently.

If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)

Maybe I'm missing something here. Since the source and destination deal with physical files only, are we talking about writing and reading to temp flat files or something? There isn't a direct way to send a data from a package to a subpackage is there?

That said, it sounds like a custom transform is probably the way to go.

Correct. The only easy way to "send" to a subpackage is via staging tables (either database tables or flat files)

consolidating multiple data/log files

Hello:
I've recently migrated a 6.5 datbase to 2000. As it was
under 6.5, the database was configured with multiple data
and log devices, which has been retained in its migrated
2000 database.
I'd like to consolidate multiple .ndf & .ldf files to a
single .mdf & .ldf file, respectively for its data and
log. Any ideas how & if its possible?
Thanks.
Rob,
First do a DBCC SHRINKFILE(..., EMPTYFILE) followed by
ALTER DATABASE <databasename>
DROP FILE ..
Refer BooksOnLiune for syntax'ndetails.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cfff01c4534e$ad942c20$a501280a@.phx.gbl...
> Hello:
> I've recently migrated a 6.5 datbase to 2000. As it was
> under 6.5, the database was configured with multiple data
> and log devices, which has been retained in its migrated
> 2000 database.
> I'd like to consolidate multiple .ndf & .ldf files to a
> single .mdf & .ldf file, respectively for its data and
> log. Any ideas how & if its possible?
> Thanks.

consolidating multiple data/log files

Hello:
I've recently migrated a 6.5 datbase to 2000. As it was
under 6.5, the database was configured with multiple data
and log devices, which has been retained in its migrated
2000 database.
I'd like to consolidate multiple .ndf & .ldf files to a
single .mdf & .ldf file, respectively for its data and
log. Any ideas how & if its possible?
Thanks.Rob,
First do a DBCC SHRINKFILE(..., EMPTYFILE) followed by
ALTER DATABASE <databasename>
DROP FILE ..
Refer BooksOnLiune for syntax'ndetails.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cfff01c4534e$ad942c20$a501280a@.phx
.gbl...
> Hello:
> I've recently migrated a 6.5 datbase to 2000. As it was
> under 6.5, the database was configured with multiple data
> and log devices, which has been retained in its migrated
> 2000 database.
> I'd like to consolidate multiple .ndf & .ldf files to a
> single .mdf & .ldf file, respectively for its data and
> log. Any ideas how & if its possible?
> Thanks.

consolidating multiple data/log files

Hello:
I've recently migrated a 6.5 datbase to 2000. As it was
under 6.5, the database was configured with multiple data
and log devices, which has been retained in its migrated
2000 database.
I'd like to consolidate multiple .ndf & .ldf files to a
single .mdf & .ldf file, respectively for its data and
log. Any ideas how & if its possible?
Thanks.Rob,
First do a DBCC SHRINKFILE(..., EMPTYFILE) followed by
ALTER DATABASE <databasename>
DROP FILE ..
Refer BooksOnLiune for syntax'ndetails.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cfff01c4534e$ad942c20$a501280a@.phx.gbl...
> Hello:
> I've recently migrated a 6.5 datbase to 2000. As it was
> under 6.5, the database was configured with multiple data
> and log devices, which has been retained in its migrated
> 2000 database.
> I'd like to consolidate multiple .ndf & .ldf files to a
> single .mdf & .ldf file, respectively for its data and
> log. Any ideas how & if its possible?
> Thanks.

consolidating member

Is it possible to add an extra member that consolidates all other members. For example, I'm looking at totals by department and account. I need an extra department that sums by account across all other departments. Like this:

Dept A

Account 1 $10

Account 2 $12

Dept B

Account 1 $15

Account 2 $11

Consolidated(A + B)

Account 1 $25

Account 2 $23

Assuming that Account and Department are separate dimensions, doesn't the "All" member, at the root of the Department hierarchy, fulfill this requirement - if not, could you explain why in some more detail?|||Yes, the "all" member does contain the data I need. My problem is in generating the final printed report. I guess I need to think about solving the problem with the reporting tool. I'm using reporting services. When I get to the end of a department, I need to display data from the "all" member which consolidates all sub departments. Then, when I get to the end of all departments, I need to display consolidating data for all departments.|||

Reporting Services does indeed present problems when dealing with an "All" member. This MSDN paper - though based on SQL Server 2000 - provides some guidance :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp?frame=true

>>

Integrating Analysis Services with Reporting Services

Sean Boon
Microsoft Corporation

June 2004

Applies to:
Microsoft SQL Server 2000

Summary: Create a compelling solution for your customer that defines and manages great-looking Analysis Services reports, and quickly answers analytical questions to improve traditional reporting scenarios.

...

Another behavior of the flattening algorithm is that the "[(ALL)]" level is not included in the dataset. This has a couple of implications. The first is that if you want to include data from the "All" member of a dimension, you'll need to create a calculated member to represent this member. This can be accomplished in a couple of different ways. The first method would be to create a calculated member on the Measures dimension and for the definition of the calculated member refer to the current member's name or unique name. There are several examples of this method represented in later sections of this whitepaper.

Note The "All" level of a dimension is not included in the field set that is returned to Reporting Services.

The second implication of the "All" level not being represented in the dataset is that calculated members, usually defined without a parent member, will need to change so that they do have a parent member. This only applies in cases where the calculated member does not belong to the Measures dimension. In many cases, when calculated members are defined on a non-Measures dimension, the parent member property is left blank. This can be changed in the calculated member dialog box as shown below

...

>>

Consolidating Like Items and Summing Totals

I need to know if there is an easy way to consolidate lines of the same item but differing quantities.

For example:

ITEM QTY
-- --
ABC 1
ABC 3
ABC 6
ABD 2
ABD 1
ABE 1

I would like to display this information as:

ITEM QTY
-- --
ABC 10
ABD 3
ABE 1

Summary: I want to consolidate those items which appear in the table more than once by combining their quantities and leaving one row that has the sum of all.

Your help is greatly appreciated.
TechRickselecting the totals is easy, right?

select ITEM, SUM(QTY)
from yourtable
group by ITEM

however, consolidating them and "leaving one row" is tricky

i suggest writing the total rows to a temporary table, deleting all rows from the original, then inserting the total rows back

create table temptable
( ITEM char(3)
, QTY integer )

insert into temptable
select ITEM, SUM(QTY)
from yourtable
group by ITEM

delete from your table

insert into yourtable
select * from temptable

drop temptable

rudy
http://rudy.ca/|||Thanks for the help. I was able to get it worked out with your suggestions.

Best Regards,
TechRick

Consolidating group names

Hello All,

I have some groups set up in a matrix that basically group by transaction names. I am trying to consolidate all but one into the same group. Right now I have the expression of...

Code Snippet

=iif(Fields!TestName.Value="Name Search","Name Search","Logon Function")

this consolidates the aggregate results and group fine but it does not label the groups as expected. "Name Search" comes up as "Name Search" but instead instead of "Logon Function", It displays the rolled up group as the name of the fist group field. Is there a way to make an alias inside of an expression?

Never mind. Answered my own question. Had to put the code into the field properties too.

consolidating folders

I have 8 folders with reports in each folder. I want to move those 8 folders into a single folder. Is there a way to do this without having to create the folder hierarchy from scratch and then upload all the .RDL's again? Thanks for your help!

frank

You can do this through the Report Manager UI using the move button. You'll need to click the "show details" button on the right hand side of the toolbar. You can do this programmatically using the MoveItem SOAP API.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||thanks Lukasz. I actually just saw it.