Conversion of Database into text file

hi All,

Actually i have a project on data minning...n i have to convert the databases into text files so that they can be consolidate ....n when consolidation of the databases(in the form of text files) would be done i have to convert the consolidated one (text file) into database again.

so anyone plz tell me dat how to convert a database (using sql server & C#) into text file...

regards,

Hello.

This is what Sql Server Integration Services was made for. Any reason why you want to create the text-files?

You have many other options.

It looks to me that what you can do with an INSERT queries using linked servers. Have a look at sp_addlinkedserver in TSQL. You might get help from reading http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html (ignore step 1 and 3)

Hope this helps.

Consolidation Query

Just need to consolidate data in SQL

Basically the data is the spend information for many suppliers, I need to churn out just the supplier name along with a sum of their amounts spent/ volume.

I need to get FROM THIS:

Company Contract Amount Spent Volume

Shell Oil Shell891 10 100
Shell Oil Shell892 20 200
BP BP0001 50 1
BP BP0001 100 2

TO THIS

Company Amount Spent Volume
Shell Oil 30 300
BP 150 3

Etc

So I need a query that can add both the amount spent and volume and then remove DISTINCT company name rows. I understand the basics of SQL but sub-queries start to confuse me!

I would be eternally grateful for any help/ advice/ tips.

Many, many thanks!Use SUM and GROUP BY as in this simple example:

SELECT deptno, SUM(sal) AS deptsal
FROM emp
GROUP BY deptno;|||please don't cross-post

http://www.dbforums.com/t1005683.html

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 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.