Best Way To Gather Large Quantities Of Data
Mar 30, 2008
Hi all,
I have a table which basically stores multiple users' responses to a questionnaire. I want to calculate certain statistics on this data (for example: how many users selected a specific answer to a question). If there are many questions and possible answers, then this can get really inefficient. I was wondering what would be the best way to go about doing this.
Currently, I was thinking of using what I believe are called crosstabs:
Code:
SELECT (SELECT COUNT(*) FROM tableName WHERE Q1answer='value1'), (SELECT COUNT(*) FROM tableName WHERE Q1answer='value2'), (SELECT COUNT(*) FROM tableName WHERE Q2answer='value1'), etc...
Is this the best way to go about this or is this really inefficient?
View 2 Replies
ADVERTISEMENT
Jul 23, 2005
Hi,I would like to prepare a data dictionary for my database (northwind).I have framed the below SQLSELECT'NAME ' = a.name,'DESCRIPTION' = b.value,'Type ' = type_name(a.xusertype),' ' AS 'Values','NULL ' = case when a.isnullable = 0 then ' ' else 'X' end,' ' AS 'PK',' ' AS 'FK'FROMsyscolumns a,sysproperties bWHEREa.id = 2073058421 AND --- Customers Tablea.number = 0 ANDb.id = a.id ANDb.smallid = a.colidORDER BY a.colidand the output would be:NAME DESCRIPTION Type Values NULL PK FK-------------- --------------- ---------- ------ ----- ---- ----CustomerID Customer ID ncharCompanyName Company Name nvarcharContactName Contact Name nvarchar XContactTitle Contact Title nvarchar XAddress Address nvarchar XCity City nvarchar XRegion Region nvarchar XPostalCode Postal Code nvarchar XCountry Country nvarchar XPhone Phone # nvarchar XFax Fax # nvarchar XPK and FK is where I need to print whether the column is Primary Key orForeign Key.If CustomerId is defined as Primary Key then PK should have X printed.Thats the objective.How will I accomplish this ?Thanks in advance,Anu
View 3 Replies
View Related
Jun 13, 2006
I have three tables:
1) Contract - columns ContractNo and ContractName
2) SalesTransactions - multiple rows for each contract - relevant columns: ContractNo, InvoiceDate, Value
3) CostOfSaleTransactions - multiple rows for each contract - relevant columns: ContractNo, TransactionDate, Cost
How do I write a SELECT statement to produce rows containing:
ContractNo
ContractName
Sum of Value for ContractNo between @FromDate and @ToDate
Sum of Cost for ContractNo between @FromDate and @ToDate
Not all Contracts have either Sales or CostOfSales Transactions in the relevant date range and so one or both totals can be zero.
I've written something like:
SELECT CT.ContractNo, CT.ContractName, sum(CT.Value) as TotalValue, Sum(CS.Cost) as TotalCost
FROM Contract CT
INNER JOIN SalesTransactions ST ON CT.ContractNo=ST.ContractNo
INNER JOIN CostOfSaleTransactions CS ON CT.ContractNo=CS.ContractNo
WHERE (ST.InvoiceDate BETWEEN @FromDate AND @ToDate) AND (CS.TransactionDate BETWEEN @FromDate AND @ToDate)
GROUP BY CT.ContractNo, CT.ContractName
The TotalValue and TotalCost figures I get are much higher than expected. I presume this is something to do with the JOINs or WHERE clause. Please can you advise how I get the correct values?
View 1 Replies
View Related
Nov 4, 2015
My data is in 4 columns and multiple rows, like this
PartNo Quantity TransactionType TransactionDate
aaa 25 Incoming 2015-03-01
aaa 25 Incoming 2015-03-01
aaa 50 Transfer 2015-03-02
bbb 30 Incoming 2015-03-03
bbb 30 Transfer 2015-03-03
ccc 50 Incoming 2015-05-15
ccc 75 Incoming 2015-05-20
ccc 50 Transfer 2015-05-18
ccc 75 Transfer 2015-05-21
What I need to achieve is sum the quantities for a given transaction type, group it by Part Number and add an additional column where the Transaction Date for Transfer Type rows would become the Transfer Date. Each part would have one row. The resulting data would look like this.
PartNo Quantity IncomingDate TransferDate
aaa 50 2015-03-01 2015-03-02
bbb 30 2015-03-03 2015-03-03
ccc 125 2015-05-15 2015-05-21
How to achieve this goal.
View 5 Replies
View Related
Mar 25, 2015
I have two tables that can be created with sample data using the DDL at the bottom of this post. What I'm looking to do is update the QtyReceived column in tblPurchaseOrderLineDetail from the Qty column in tblReceivedItems. However, the tricky part that I can't figure out is splitting these quantities out over multiple lines. I should only be allowed to receive up to the QtyOrdered column in tblPurchaseOrderLineDetail.
For a specific example from the sample data we'll look at PurchaseOrderDetailID 28526. From the tblReceivedItems, there are three records with quantities of 48, 48, and 20. From the tblPurchaseOrderLineDetail there are three records of QtyOrdered of 55, 45, and 20. What I would like to happen is fulfill the records in the tblPurchaseOrderLineDetail sequentially (essentially in order of ExpectedDate). So, the QtyReceived would be 55, 45, and 16 for the corresponding records. If there is already a quantity in the QtyReceived column, but it's less than the QtyOrdered column, the quantity needs to be added to the column (not overwritten).
DDL To CREATE Sample Tables and Data:
CREATE TABLE [dbo].[tblReceivedItems](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PurchaseOrderDetailID] [int] NULL,
[Qty] [int] NULL)
SET IDENTITY_INSERT [dbo].[tblReceivedItems] ON
INSERT [dbo].[tblReceivedItems] ([ID], [PurchaseOrderDetailID], [Qty]) VALUES (1, 28191, 48)
[code]....
View 5 Replies
View Related
May 17, 2004
Hi folks
I wonder if anyone has any pointers on how to gather statistics for SELECT queries? For instance, say 10 rows are returned by a query, is it possible to log which rows where returned?
TIA
Jarud
View 3 Replies
View Related
Jun 20, 2001
Can anyone direct me to either some code or reading, that talk about gather all my server information. What I want to do is gather all the server Used and Free space size. I'm know a little sql-dmo, and I think I can do it that way, but not sure.
View 1 Replies
View Related
May 4, 2006
The IT group that I work with has the habit of gathering data,formatting (i.e. in reports) and then storing the same formated data inthe same database.I think the practice is wrong. I think the activity is fundamentallywrong because we are storing the exact same data in a database in twodifferent locations. Somehow I have the impression that database designis about "oneness".I believe that collecting the data and then storing summerized data forreporting into a data warehouse would be the right solution.I am getting flack for my viewpoint.Am I all washed up?
View 1 Replies
View Related
Jan 21, 2015
I wanted to schedule the transaction replication. How do I do it? Currently I have set up a transaction replication which runs continuously and synchronizes the changes with immediate effect.
I need to configure a replication which will gather logs from the publication once in a day.
Is there any possibility?
View 1 Replies
View Related
Oct 4, 2015
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
View 4 Replies
View Related
Oct 4, 2006
hello guyshere is my problem:i am developing a asp.net web app in .net 2.0. i have some sensitive data in my database. which is encrypted using DES ( with some key which is only known by the top level authorities ). now there is an option of changing the secret key. on changing the key the sensitive data has to decrypted using the old key and then again encrypted using the new key. Now if the no of records increases i am afraid that it might take a longer time and the application might look as it got hanged. guys i have no clue on how to do this. if you guys have any idea on how to implement this please let me know. any help would be appreciatedVignesh
View 7 Replies
View Related
Mar 20, 2008
Hi,
I'm currently trying to retrieve results from a large dataset, there are over 45000 records and I need to use them all to peform counts etc. I have set up views, but my page is still being returned slowly, is there anything I can do to speed this up?
Thanks
Gemma
View 2 Replies
View Related
Mar 19, 2001
I was wondering if anyone can help me.
I am trying to import data into SQL Server 7. The table will be 700-800 columns, and the data will be about 150,000 records at a time.
The data source is flat file.
First I create the table using a database schema, and secondly I would like to populate the table.
The problem is that most of the data is numeric, and to be used for statistical analysis.
So far I have tried Bulk Insert, bcp, and dts.
DTS is the only method that has worked in any way, shape or form, but that requires importing each column as a Varchar. Importing to my pre-created table doesn't work, because it is interpreting some of the source columns as character data and refusing to insert them into an int field.
Bulk Insert and bcp both give error messages, and I am wondering if that is because of the size of the insert statement that is required to handle so many fields.
For the moment I am just trying to import the data in any way, but eventually, it will have to be run as an automated process, with the table structure probably needing to be altered as well.
Any help/suggestions would be very greatfully received.
View 2 Replies
View Related
Feb 11, 2004
I have a web site that allows user to enter large strings into a database (comments, etc). What is the best way to do that? Right now I have them limited to 25 characters and the data type is varchar. Is there a better way?
Thanks!
View 2 Replies
View Related
Dec 22, 2014
when to use table variable and temp table. i told the interviewer that when rows is less like hundreds or thousand then use table variable else use temp table.After that he asked that what do u mean by less data or thousand rows may be there are multiple columns involved with that less rows and make a huge data set.
View 3 Replies
View Related
Jul 20, 2005
I want to build a system that will have about 1 million rows in atable in sql server database.I am using this for a web application andaccessing it via JDBC type 4 driver.But display 20 records at a timeonly using pagination(as in google).What will be the best way to goabout this.
View 1 Replies
View Related
Jul 20, 2005
I have a database that is 70GB big. One of the tables has over 350million rows of data. I need to delete about 1/3 of the data in thatone table.I was going to use a simple delete command to delete the unnessacaydata.Something likeDelete from tablename where columname > '100'However, it takes SOOO LONG to run, over 8 hours, if not longer....What is the best way to delete this data???One idea was to create a Temp Table, then move the needed data to thetemp table, then Truncate the Table, and move that data back.I'm not very good with SQL Query Language, so can somone give me anexample on how to do this?? Or if you have a differant idea that wouldbe faster please let me know.thanks,Sam
View 2 Replies
View Related
Jul 20, 2005
HiI have a SQL2000 server with 128m rows of data. I want to delete about65m of that. So far I have bcp'ed the relevent data out and put theminto another SQL database.We have a small amount of space for our transaction log so I cannotdelete all 65m rows in one go. So far I have been doing them is 0.5mchuncks, but it is extremly slow.Would a faster way be to bcp the data I wan to keep and truncate thetable and bulk import them in again ?What hapnes to log size in when builk import is happening and is thereanother way of doing this ?Thanks for any help
View 2 Replies
View Related
Jun 5, 2006
I want to store some binary things(pic and so on), so I create a table which contain a a "varbinary" data-type column.
but 1. I used OPENROWSET to insert the large file in this table. 2. I used master..xp_cmdshell to retrieve data out as a file. One strange thing happened: the size of the input and output is really different(output is 1k bigger than the input file).
and it seems that the file is broken with different file format......
I really don't know why....
Any help would be appriciated.....
kavin
View 3 Replies
View Related
Apr 10, 2001
Hi All,
How do I input a large text page (notepad) into a SQL column. Or assign a pointer to the data. I've tried to use BOL (writetext) and to no avail, I guess I'm missing something. I'm just using EM and Query analyzer. I thought this should be easy. Image data should work the same way.
TIA,
Dave
View 1 Replies
View Related
Oct 31, 1999
Hello:
The purchased-application mssql 6.5, sp 4 that I am working on has one large table has 13m illion. It the largest table considering thenextlatgest table is only1.75 million rows.
Thew vnedor has made a change to this largest table in recommending changing a data type -- char to varchar. To make this change easier to do,
I want to "archive" older data not necessary for the current year or current processing to another table.
What is the best way to do this archiving?
Any information you can provide will be greatly appreciated. Thanks.
David Spaisman
View 4 Replies
View Related
Jul 16, 2015
I have run the select query which returned one row. There is one column in it which has got large amount of data. I want to copy the complete content of that column(varchar(max)), but I am unable to do it. It's not the xml data. I don't want to do any conversions.
View 1 Replies
View Related
Jan 12, 2006
hello
i have just created a test database and now need to insert a large number of records into one of the tables, we were thinking of about 1 million records, has anyone got an sql script that i could use to create these records
cheers
john
View 6 Replies
View Related
Mar 21, 2008
Hi i wanna delete all the records from an large database 200 -300 tables, because i want make some changes an start from scratch,but keep the structures of the database key , index etc, i tried to generate script but when i run to many errors , plz help 10x
View 11 Replies
View Related
Mar 31, 2008
Hi,
I was wondering if any one could help me, I need to store large amounts of data in my database, at present I have it set to nvchar (8000), I've looked around and noticed you can use text which stores up to 2 million, but is slow in displaying the information.
Any ideas or points in the right directions would be great.
Thanks
View 6 Replies
View Related
Sep 8, 2005
Does anyone have ideas on the best way to move large amounts of databetween tables? I am doing several simple insert/select statementsfrom a staging table to several holding tables, but because of thevolume it is taking an extraordinary amount of time. I consideredusing cursors but have read that may not be the best thing for thissituation. Any thoughts?--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict254055.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=877392
View 4 Replies
View Related
Oct 7, 2005
This is a general question on the best way to import a large amount of datato a MS-SQL DB.I can have the data in just about any format I need to, I just don't knowhow to import the data. I some experience with SQL but not much.There is about 1500 to 2000 lines of data. I am looking for the best way toget this amount of data in on a monthly basis.Any help is greatly thanked!!Mike Charney
View 5 Replies
View Related
Feb 2, 2006
Brief background:We are using SQL Server 2000, and one of the tables stores usersessions details (each time our users logs into our system we insert anew record in the session table, and each time user logs out from oursystem we insert another record in the same table).SESSION_ID is the primary key and it is clustered index.The system produces 5 million session records/day.The problem:Each day we transfer the session data (delta only) to other machine andwe want to delete bulk of ~5 million sessions. This should happendwithout any interfering of our customers activity ( in the same time,we should not block the table - new sessions should be created).What is the best way to perform such task ?
View 4 Replies
View Related
Jul 20, 2005
We are looking to store a large amount of user data that will bechanged and accessed daily by a large number of people. We expectaround 6-8 million subscribers to our service with each record beingapproximately 2000-2500 bytes. The system needs to be running 24/7and therefore cannot be shut down. What is the best way to implementthis? We were thinking of setting up a cluster of servers to hold theinformation and another cluster to backup the information. Is thispractical?Also, what software is available out there that can distribute querycalls across different servers and to manage large amounts of queryrequests?Thank you in advance.Ben
View 10 Replies
View Related
Aug 31, 2006
Hi guys,
Hopefully this is the right place to ask.
Basically we have have two larges databases, one of which is updated from the other monthly.
For exaplination purposes:
DB1 = Source DB
DB2 = Destination DB
The problem that I require a soltion to is, how do I insert rows from a table in DB1 to DB2 and recover and store the identity of the new row against the ID of the existing row. This is so that I can then matain constraints when it comes to inserting rows into the next table and the next and so on.
This process of storing the ID's as lookups will need to be done for almost every table of which there are 20.
The best Idea we have at the minute is to create a table with two colums for each table (drop it and recreate it after each table has exported) that contains the two ID's, new and old.
This will require using a cursor for each row in the existing table, inserting it in the new table and the using @@Scope_Identity to get the new ID and then insert the two values into the temp table.
This too me feels like it will be very slow, particuarly when I bear in mind how much data we have.
Does anyone have any better ideas? (Sorry if the explaination isn't great, its difficult to get accross)
Thanks
Ed
View 1 Replies
View Related
Aug 16, 2007
We can pass XML to the XML Source in a variable, but I haven't seen anywhere how much data can be passed this way? Is there a limit beyond the limits of system memory?
Also, what data types are valid for the variable? Just String?
View 4 Replies
View Related
Jun 24, 2005
I have a dataset with 300,000 records and I'm getting the following error with MS Reporting Services. "An error has occurred during report processing. Exception of type System.OutOfMemoryException was thrown. any help with this would be highly appreciated.
View 11 Replies
View Related
May 8, 2007
Greetings
I need to be able to graph roughly about 150 employees/ supervisor and their monthly cell phone usage in minutes. I understand that I will need to group this on say one graph for every ten employees so it doesn't look messy and cluttered. I have read some threads here but they dont seem to work for me.
So again each supervisor has 100+ subordinates and I need to graph theie phone usage by month
thoughts???
km
View 2 Replies
View Related
