Sample Data Required
Feb 13, 2007
Sample data required
Hello.
Though this is not directly a SQL related question but I could not think of some other forum to post this request. <y
apologies if I should have posted elsewhere...
I need some sample data for a departmental store (like WalMart of KMart) which sell a wide variety of things and probably
everything under the sun :)
I need data in such a manner that we have 4-5 levels of data hierarchy. For example we can have a data hierarchy like
follows:
Classification
Category
Sub Category
Product Group
Item
The above is just an example but I hope that most of you already know what I neeed. An example of data based on the following
hierarchy can be as follows (shown in a tabular manner)
Classification | Category | Sub Category | Product Group | Item
---------------------------------------------------------------
Household Items| Electronics| Entertainment| Audio| SONY Room entertainment box, 2 speakers 300 W each, ....
Household Items| Electronics| Entertainment| Audio| Phillips Boom Box, ...specifications....
Household Items| Electronics| Entertainment| Video| SAMSUNG DVD Player model SG-17X5 ...
Household Items| Electronics| Entertainment| T.V | SONG TRINTORN 51" .....
...
...
Groceries | Drinks | Colas| Coke | COke 12-Pack Cans ....
Groceries | Drinks | Colas| Coke | COke 2.25 Litre Jumbo Pack..
Groceries | Drinks | Colas| PEPSI| PEPSI 2.25 Litre Jumbo Pack..
.....
...
Toys | Girls | Dolls| Barbie | Barbie princees model A21 ..
...
..
I hope the above provides you with a clear understanding of my requirement.
I'll be extremely grateful for your help.
Thanks & Regards.
View 3 Replies
ADVERTISEMENT
Feb 13, 2007
Hello.
Though this is not directly a SQL related question but I could not think of some other forum to post this request. <y apologies if I should have posted elsewhere...
I need some sample data for a departmental store (like WalMart of KMart) which sell a wide variety of things and probably everything under the sun :)
I need data in such a manner that we have 4-5 levels of data hierarchy. For example we can have a data hierarchy like follows:
Classification
Category
Sub Category
Product Group
Item
The above is just an example but I hope that most of you already know what I neeed. An example of data based on the following hierarchy can be as follows (shown in a tabular manner)
Classification | Category | Sub Category | Product Group | Item
---------------------------------------------------------------
Household Items| Electronics| Entertainment| Audio| SONY Room entertainment box, 2 speakers 300 W each, ....
Household Items| Electronics| Entertainment| Audio| Phillips Boom Box, ...specifications....
Household Items| Electronics| Entertainment| Video| SAMSUNG DVD Player model SG-17X5 ...
Household Items| Electronics| Entertainment| T.V | SONG TRINTORN 51" .....
...
...
Groceries | Drinks | Colas| Coke | COke 12-Pack Cans ....
Groceries | Drinks | Colas| Coke | COke 2.25 Litre Jumbo Pack..
Groceries | Drinks | Colas| PEPSI| PEPSI 2.25 Litre Jumbo Pack..
.....
...
Toys | Girls | Dolls| Barbie | Barbie princees model A21 ..
...
..
I hope the above provides you with a clear understanding of my requirement.
I'll be extremely grateful for your help.
Thanks & Regards.
View 4 Replies
View Related
Feb 13, 2007
Hello.
Though this is not directly a SQL related question but I could not think of some other forum to post this request. <y
apologies if I should have posted elsewhere...
I need some sample data for a departmental store (like WalMart of KMart) which sell a wide variety of things and probably
everything under the sun :)
I need data in such a manner that we have 4-5 levels of data hierarchy. For example we can have a data hierarchy like
follows:
Classification
Category
Sub Category
Product Group
Item
The above is just an example but I hope that most of you already know what I neeed. An example of data based on the following
hierarchy can be as follows (shown in a tabular manner)
Classification | Category | Sub Category | Product Group | Item
---------------------------------------------------------------
Household Items| Electronics| Entertainment| Audio| SONY Room entertainment box, 2 speakers 300 W each, ....
Household Items| Electronics| Entertainment| Audio| Phillips Boom Box, ...specifications....
Household Items| Electronics| Entertainment| Video| SAMSUNG DVD Player model SG-17X5 ...
Household Items| Electronics| Entertainment| T.V | SONG TRINTORN 51" .....
...
...
Groceries | Drinks | Colas| Coke | COke 12-Pack Cans ....
Groceries | Drinks | Colas| Coke | COke 2.25 Litre Jumbo Pack..
Groceries | Drinks | Colas| PEPSI| PEPSI 2.25 Litre Jumbo Pack..
.....
...
Toys | Girls | Dolls| Barbie | Barbie princees model A21 ..
...
..
I hope the above provides you with a clear understanding of my requirement.
I'll be extremely grateful for your help.
Thanks & Regards.
Thanks & Regards.
-J
View 4 Replies
View Related
Jul 11, 2007
Does Any one have a SQL server sample database atleast with 50 mb ofdata or can anyone give me a link with I could download it?RegardsSathish S N
View 3 Replies
View Related
Mar 28, 2006
Hi
Books online mention the existence of sample code for several custom tasks, including the one mentioned in the title. But, when I try to find this code in the location mentioned it is nowhere to be found.
I have run a search on the rest of my drive and come up empty.
Can anyone tell me where to find this?
Thanks
View 3 Replies
View Related
Feb 29, 2008
Im trying to use VB.net 2005 to write a sample app to access a DB. Are there any samples for this and any samples of how I go about making the DB in the first place?
View 1 Replies
View Related
Sep 18, 2005
I am needing to o exactly what Zippygoose says (with ordered ID numbers). How do yo make an insert statement that will make a loop until let's say the ID reaches 8000??
Thanks in advanced,
Edit:
User needs to fill his tables with sample data.
(Sorry for the edit, but I prefer to split this thread instead of continuing an old one (2003!!)).
View 1 Replies
View Related
Mar 6, 2007
Here’s a more in depth breakdown of my problem:
We have 4 regions, currently we only have 3 servers in the field, and therefore only 3 regional id’s are being used to store the actual data of the pbx. The central server (RegionalID = 0) is holding the data for itself and the 4th region until the new server is deployed.
It now has to be deployed and therefore the data migration for this region has to take place.
I am trying to extract all the data for this 4th region (RegionalID= 1) from the central server database from all the relevant tables.
When doing this I will firstly, have to check that the CallerID is valid, if it is, send that entry along with the result set, if it is not valid, Check that the dongle area code is valid, if dongle area is valid select with the result set, and if it is not valid, then check that RegionalDialup = ‘0800003554’ which is the dialup number for this 4th region (RegionalID = 1).
I have a table named lnkPBXUser which contains the following:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[lnkPBXUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[lnkPBXUser]
GO
DDL:
CREATE TABLE [dbo].[lnkPBXUser] (
[RegionalID] [int] NOT NULL ,
[pbxID] [decimal](18, 0) NOT NULL ,
[userID] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
GO
Sample data:
INSERT INTO lnkPBXUser(RegionalID, pbxID, userID)
SELECT 0, 543, 2 UNION ALL
SELECT 0, 10961, 6 UNION ALL
SELECT 0, 1012, 17 UNION ALL
SELECT 0, 16499, 26 UNION ALL
SELECT 0, 14061, 36 UNION ALL
SELECT 0, 16499, 2
I have a table named tblDialupLog which has 20 columns, I have selected only the columns I am interested in (below):
PBXID DailupDT DongleAccessNum CLI RegionalID RegionalDialup
83 8/8/2006 8:58:11 AM T2 UQ 28924 0132493700 0 0800003554
543 8/8/2006 8:55:44 AM T0 UA 33902 0123623500 0 0800003554
1219 8/8/2006 8:59:03 AM T3 ZD 02031 0152958095 0 0800003554
1012 8/8/2006 9:02:54 AM T0 UA 41261 0173011050 0 0800003554
1331 8/8/2006 8:59:57 AM T0 UA 01938 0124604627 0 0800003554
1979 8/8/2006 9:02:52 AM T0 UA 09836 0163751210 0 0800003554
1903 8/8/2006 8:58:41 AM T0 UA 26009 0147175356 0 0800003554
1522 8/8/2006 8:58:54 AM T3 MB 94595 0573912871 0 0800004249
319 8/8/2006 8:51:28 AM T2 ZD 32892 0543375100 0 0800004249
3270 8/8/2006 9:04:26 AM T2 MB 87331 0 0800004249
DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDialupLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDialupLog]
GO
CREATE TABLE [dbo].[tblDialupLog] (
[RegionalID] [int] NOT NULL ,
[PBXID] [int] NULL ,
[DialupDT] [datetime] NULL ,
[DongleAccessNum] [varchar] (64) NULL ,
[CLI] [varchar] (64) NULL ,
[RegionalDialup] [varchar] (50) NULL
) ON [PRIMARY]
GO
Sample data:
INSERT INTO tblDialupLog(PBXID,DailupDT ,DongleAccessNum,CLI,RegionalID,RegionalDialup )
SELECT 83,'8/8/2006 8:58:11 AM' ,'T2 UQ 28924','0132493700',0 , '0800003554' UNION ALL
SELECT 543,'8/8/2006 8:55:44 AM','T0 UA 33902','0123623500',0,'0800003554' UNION ALL
SELECT 1012, '8/8/2006 9:02:54 AM', 'T0 UA 41261', '0173011050', 0 ,'0800003554' UNION ALL
SELECT 1219, '8/8/2006 8:59:03 AM' ,'T3 ZD 02031', '0152958095', 0,'0800003554' UNION ALL
SELECT 16499, '8/8/2006 8:51:28 AM', 'T2 ZD 32892', '0543375100', 0, '0800004249'
You see that the DongleAccessNumber is actually made up of three parts, and it is the middle part (ie.UQ) which i will use to check that the tbldongleArea.DongleAreaCode is valid for that region
I have a table named tblCodes, it contains all regions but I only need to select the codes for RegionalID 1 :
CodeID RegionalID ExtName SubsNDCD LocCD UpdateStatus RegionDesc
7973 1 PRETORIA 012 362 0 NORTH EASTERN REGION
7974 1 HARTEBEESHOEK 012 3012 0 NORTH EASTERN REGION
7975 1 HARTEBEESHOEK 012 3013 0 NORTH EASTERN REGION
7976 1 PRETORIA 017 3014 0 NORTH EASTERN REGION
7977 1 PRETORIA 012 3015 0 NORTH EASTERN REGION
DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCodes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCodes]
GO
CREATE TABLE [dbo].[tblCodes] (
[CodeID] [int] NOT NULL ,
[RegionalID] [int] NULL ,
[ExtName] [varchar] (64) NULL ,
[SubsNDCD] [varchar] (10) NULL ,
[LocCD] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL ,
[RegionDesc] [varchar] (255) NULL
) ON [PRIMARY]
GO
Sample Data:
INSERT INTO tblCodes(CodeID ,RegionalID ,ExtName , SubsNDCD ,LocCD ,UpdateStatus,RegionDesc)
SELECT 7973,1, 'PRETORIA', '012', '362', 0 ,'NORTH EASTERN REGION' UNION ALL
SELECT 7974,1, 'HARTEBEESHOEK ', '012', '3012', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7975,1, 'HARTEBEESHOEK ', '012', '3013', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7976,1, 'PRETORIA', '012', '3014', 0,'NORTH EASTERN REGION' UNION ALL
SELECT 7977,1, 'PRETORIA', '017', '3015', 0,'NORTH EASTERN REGION'
I have a table named tblDongleArea which contains the following (below only shows dongle area codes for the fourth region( RegionalID = 1):
AreaID RegionalID DongleAreaCode AreaDesc UpdateStatus
12 1 UA Oumashoop 0
13 1 UB Pietersburg 0
14 1 UC Warmbad 0 1
15 1 UD Nylstroom 0
16 1 UE Potgietersrus 0
27 1 UF Louis Trichardt 0
28 1 UG Messina 0
DDL:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDongleArea]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDongleArea]
GO
CREATE TABLE [dbo].[tblDongleArea] (
[AreaID] [int] NOT NULL ,
[RegionalID] [int] NULL ,
[DongleAreaCode] [varchar] (5) NULL ,
[AreaDesc] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL
) ON [PRIMARY]
GO
Sample Data:
INSERT INTO tblDongleArea(AreaID,RegionalID,DongleAreaCode,AreaDesc,UpdateStatus)
SELECT 12,1, 'UA', Oumashoop, 0 UNION ALL
SELECT 13, 1, 'UB', 'Pietersburg', 0 UNION ALL
SELECT 14, 1 ,'UC' ,'Warmbad', 0 UNION ALL
SELECT 15, 1, 'UD', 'Nylstroom', 0 UNION ALL
SELECT 16, 1, 'UE', 'Potgietersrus', 0 UNION ALL
SELECT 27, 1, 'UF', 'Louis Trichardt', 0 UNION ALL
SELECT 28, 1, 'UG', 'Messina', 0
I have a table named tblRegionalNumbers which contains the following, as you can see the RegionalDialup for the fourth region = 0800003554:
RegionalID RegionalDialup Region UpdateStatus RegionCode LocalRegion
1 0800003554 North Eastern 0 1 0
2 0800005027 Gauteng 0 2 0
3 0800006194 Eastern 0 3 0
4 0800004249 Central 0 4 0
5 0800201859 Southern 0 5 0
6 0800201989 Western 0 6 0
7 0800113515 HO 1 0 1
8 0800222204 Tellumat 0 7 0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblRegionNumbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblRegionNumbers]
GO
CREATE TABLE [dbo].[tblRegionNumbers] (
[RegionalID] [int] NOT NULL ,
[RegionalDialup] [varchar] (255) NULL ,
[Region] [varchar] (64) NULL ,
[UpdateStatus] [int] NULL ,
[RegionCode] [int] NULL ,
[LocalRegion] [int] NULL ,
) ON [PRIMARY]
GO
INSERT INTO tblRegionNumbers(RegionalID ,RegionalDialup,Region,UpdateStatus,RegionCode,LocalRegion)
SELECT 1,'0800003554', 'North Eastern', 0, 1, 0 UNION ALL
SELECT 2, '0800005027' ,'Gauteng', 0 ,2, 0 UNION ALL
SELECT 3, '0800006194','Eastern', 0, 3, 0 UNION ALL
SELECT 4, '0800004249' ,'Central', 0, 4, 0 UNION ALL
SELECT 5, '0800201859','Southern', 0 ,5, 0 UNION ALL
SELECT 6, '0800201989' ,'Western' 0, 6, 0 UNION ALL
SELECT 0, '0800113515', 'HO', 1, 0, 1 UNION ALL
SELECT 8, '0800222204', 'Tellumat', 0, 7, 0
Ok, I am dealing with the lnkPBXUser table at the moment,
I need to be able to join lnkPBXUser and tblDialupLog, then compare tblDialupLog.CLI to tblCodes.SubsNDCD + tblCodes.LocCD (when these two columns are concatenated the result will only be a substring of tblDialupLog.CLI. (this is to make sure that the CLI exists in tblCodes.)
If it does exist, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDongle.DongleAreaCode is a substring of tblDialupLog.DongleAccessNumber.
If it is a valid DongleAreaCode for that region, then it is part of the fourth region and should be returned in the result set.
If it does not exist, I then need to check that tblDialupLog.RegionalNumber = ‘080003554’.
So from the above tables an expected result would be:
RegionalID pbxID userID
0 1012 17
0 543 2
Please assist, it would be greatly appreciated.
Regards
SQLJunior
View 20 Replies
View Related
Jul 20, 2005
I want the sum of the last payments (amount) for all customers. The lastpayment is with one with most recent date. And if there are more than onepayment on the most recent date then the one with the higher paymentid isthe last payment. for example in the given data the insert statement thatstarts with capital I is the last payment of that customer. The correctanswer should be 2100 as given below. both queries by Erland and Anith givethe result 100 ( I removed the "WHERE p1.date <= '20030301' " Clause fromboth queries since right now I want current sum (not till some date). Sowhat should be the right query.Thanks again for the help.create table payments (paymentid int,customerid int,amount int,date datetime)insert payments values (1, 1, 100, '1/1/03')insert payments values (2, 1, 200, '2/28/03')Insert payments values (3, 1, 500, '5/15/03')insert payments values (4, 2, 400, '1/16/03')insert payments values (9, 2, 800, '4/30/03')insert payments values (5, 2, 200, '6/15/03')Insert payments values (6, 2, 900, '6/15/03')insert payments values (7, 3, 700, '3/1/03')insert payments values (10,3, 300, '7/10/03')Insert payments values (8, 3, 600, '9/1/03')insert payments values (11,4, 300, '8/1/03')insert payments values (12,4, 400, '9/10/03')Insert payments values (13,4, 100, '9/10/03')customerid lastpayment amount1 3 (on 5/15/03) 5002 6 (on 6/15/03) 9003 8 (on 9/1/03) 6004 13 (on 9/10/03) 100========Result => 2100
View 4 Replies
View Related
Jun 8, 2006
hi, i am bit new in the field of data warehousing and data mining but catching up fast. I am in the last year of BS(Computer Science) and intend to do a project on a Business Intelligence but i was wondering can i get a sample dummy data for my work is that any way possible??
View 1 Replies
View Related
Jun 26, 2003
Does anyone have a sample RFP for a Data Warehousing project?
My manager hired an outside consultant to draw up a proposal for our company. But it is getting stuck in details and we are way behind schedule.
It will help me greatly if there is an outline of a DW RFP.
View 4 Replies
View Related
Jun 11, 2008
I created a database using SSMS, now I want to add sample data.
What are the methods of adding sample data from the server?
Do I need to use the SQL Server client or can I use the SQL command line or query window?
Thank you,
Goldmember
View 1 Replies
View Related
Jul 20, 2005
SQL Server 2000Is there any way to take sample data in my database and create an INSERT INTO script?I have a commercial application that I would like to include sample data, and instead of restoring a backup like I am doing now, I would like to first run a script that creates the database, stored procedures, etc, then run a script that inserts sample data if the customer so chooses.I know I can do this manually, but is there any way to create the script based on exisiting data?TIA-- Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.http://www.vehiclewebstudio.com
View 4 Replies
View Related
May 9, 2007
Hello
I'm looking for sample database for data mining in SQL Server 2005. In my project I need to use all algorithms provided by SQL Server 2005. I know that we have "SQL Server 2005 Samples and Sample Databases" on microsoft.com site, but there are only AdventureWorks data sets. I need sth less popular, but similar.
I have found http://www.kdnuggets.com/datasets/index.html and others, but I don't see data sets good for multidimensional analysis.
The origin of data may be natural or artificial, it doesn't matter.
I don't have to generate my own data, because I had to discover new knowledge, that will be surprising to me.
Any help would be appreciated.
Thanks in advance.
(sorry for my English)
View 1 Replies
View Related
Mar 26, 2007
Hi, there;
I try to import data from ODBC using C# programmatically. Is there any sample code we can have a look. Like how to read schema from ODBC source table and then create source column...
Thanks
View 3 Replies
View Related
Oct 17, 2007
Hi,
I'm working with the sample database called Adventure Works and I'm encountering data consistency issues :
If I sum the TotalDue for one particular territory, for one particular time window from the Sales. SalesOrderHeader table and if I try to compare the result to the LastYearSales and YTDSales from the Sales.SalesTerritory table, I don't get the same number, whatever I try. So did I miss something ?
How are the LastYearSales and YTDSales fields from Sales.SalesTerritory calculated ?
Thx,
Anissa
View 3 Replies
View Related
May 21, 2008
help i run this
from this link
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=NumbersTable&referringTitle=Home
and it stuck my sql server!!!!!
and after i run this
my my mdf ldf is 6 GB
Code Snippet-- Suppress data loading messages
SET NOCOUNT ON
-- Create Sample Data using a Table Varable
SELECT TOP 2147483647 IDENTITY(INT,0,1) AS N
INTO Numbers
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d, sysobjects e
before i was 6 mb
help
now how to fix this problem and shrinking my data
View 8 Replies
View Related
Nov 1, 2007
Hi! I am new to SQL Server... looking for some veteran assistance.
"Data Integrity Report"
I need a Stored Procedure that takes a table name as a parameter and returns a cursor suitable as a data source for a pre-built Report Services report (I guess Report Services would call the SP?).
The cursor/report needs to have the following columns:
Ordinal_Position (I.E. Column number)
Column_Name
Number Of Blank Rows (how many missing values for this column in this table)
Difference (Between total rowcount and population of this column)
Data_Type
Column_Length (either Character_Maximum_Length or the numeric widths rolled up with COALESCE?)
Sample Data (The contents of the "first" row in the table, based on a TOP(1) and ORDER BY xxx)
The report should look like this (for a table with 100 rows):
Col Num Col Name # Blanks Difference Data Type Col Length Sample Data
1 Name 12 88 varchar 30 Sally Smith
2 Address 34 66 varchar 45 123 Main St Apt 45
3 Acct_ID 0 100 varchar 4 AB12345
Using the "Information_Schema.Columns" I can get everything I need except for #3 (blanks count) and #7 (Sample data).
Is it possible to do this as 1 query, with a CTE or APPLY or something, or do I need to do a table variable based on the Information_Schema and then use dynamic SQL and row-by-row COUNT(*) for each column? And the same for the Sample Data.
Sorry for the long post, and thanks in advance!
John
View 1 Replies
View Related
Nov 13, 2007
Hello, can anyone tell me if it is possible to conditionally join tables? If so, how could it be done?
What I would like to do is create a query similar to the one below but include the rows in the Asset table where the corresponding Alert field is NULL.
SELECT A.AssetId, A.IndustryId, A.RegionId, A.RevenueId, AL.AlertId
FROM Alerts AS AL INNER JOIN
Assets AS A ON AL.IndustryId = A.IndustryId AND AL.RegionId = A.RegionId AND AL.RevenueId = A.RevenueId
WHERE AlertId = 1
The output I am after would be the first row in the Assets table. But since the RevenueId column is NULL, I get nothing. The only time the above query will work is if all three Id columns are populated. That is not always going to be the case. Please don't suggest changing table structures or adding data. That is not an option.
Below are the table structures and sample data.
CREATE TABLE [dbo].[Alerts](
[AlertId] [int] NOT NULL,
[IndustryId] [int] NULL,
[RegionId] [int] NULL,
[RevenueId] [int] NULL,
CONSTRAINT [PK_Alert] PRIMARY KEY CLUSTERED
(
[AlertId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Assets](
[AssetId] [int] NOT NULL,
[IndustryId] [int] NOT NULL,
[RegionId] [int] NOT NULL,
[RevenueId] [int] NOT NULL,
CONSTRAINT [PK_Assets] PRIMARY KEY CLUSTERED
(
[AssetId] ASC,
[IndustryId] ASC,
[RegionId] ASC,
[RevenueId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (1, 2, 5, NULL)
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (2, 2, 5, 1)
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (3, 2, NULL, NULL)
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (4, NULL, 5, NULL)
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (5, 3, NULL, 4)
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (6, NULL, 4, NULL)
INSERT INTO Alerts (AlertId, IndustryId, RegionId, RevenueId)
VALUES (7, 3, 4, 1)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (1, 2, 4, 3)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (1, 2, 5, 1)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (2, 2, 5, 5)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (2, 3, 4, 5)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (3, 2, 4, 1)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (4, 2, 5, 4)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (5, 3, 5, 1)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (5, 2, 4, 5)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (5, 3, 1, 1)
INSERT INTO Assets (AssetId, IndustryId, RegionId, RevenueId)
VALUES (6, 3, 2, 4)
Thanks.
View 7 Replies
View Related
Dec 1, 2006
Hi, all here,
Thank you very much for your kind attention.
I dont think we should sample any nested tables for data mining model training? Since I think any nested tables are bound to the case table. Therefore whenever we sample the case table, the nested tables are like any other input attributes within the case table to be rectrieved as inputs accordingly?
Thank you very much for any guidance to clear my confusion.
With best regards,
Yours sincerely,
View 3 Replies
View Related
Jun 9, 2007
Dear all
i am newbie to this forum as well as learning SQL so don't have much knowledge.
i want one query which will transpose data in column
i have 3 fileds
Scrip--COC--DAte
A--25--01/06/07
B--34--01/06/07
C--65--01/06/07
A--65--02/06/07
B--98--02/06/07
C--37--02/06/07
now i want a query which shows data in this way
SCRIP--01/06/07--02/06/07--03/06/07
A--25--65--....
B--34--98--....
C--65--37--....
now this date column will be only 5 i.e. 1 which user puts + 4 preceding dates from user date (ignoring sequence means if user put 10/06/07 then next will be 09/06/07 but if this wont exists then take 08/06/07 & so on till 4 dates)
the data will be unique means no scrip repeats more than once in a day
pl reply soon
View 1 Replies
View Related
Jan 22, 2007
I have a report with this data query:
select ID1 = 6, ID2 = 15, ID3 = 3, ID4 = 3
The data is evidently static here, and hence database access is not required.
Is there a way to omit the connection string in the Datasource, or have a disconnected Datasource that does not connect to any database?
Currently I am setting the connection string arbitrarily, but it would be ideal to omit it altogether.
TIA,
ana
View 1 Replies
View Related
May 6, 2008
Hello,
I am trying to get results in one row from the following function but all records does not come from the following function.
I have 9 records of the same empid but results not showing all records. Can anybody help me to get all records with the fields and table which I am using.
My table is beneficiary and fields are empid int, fname char(40), benefittype char(4) and benefitpercentage .
Following function is working but problem is not getting all results in one row, as described below.
CREATE FUNCTION dbo.GetBenefString5
(
@Empid INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @ret VARCHAR(8000)
SELECT @ret = ''
SELECT
@ret = @ret + CASE WHEN LEN(@ret) > 0 THEN ',' ELSE '' END + FName + ' ' + Benefittype + ' ' + BenefitPercentage
From Beneficiary
Where Empid = @Empid
RETURN @ret
END
SELECT
Empid,
dbo.GetBenefString5(Empid)
FROM pf25eaton_work.dbo.eaton_chr_benef_05052008
where EmployeeNumber='4500498'
GROUP BY Empid
Result from the above function query:
Empid ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1773 CHARLENE OLIF 33 ,CHARLENE EADD 33 ,CHARLENE ELIF 33 ,TIMOTHY EADD 33 ,TIMOTHY
(
Records are in the table: (But I have many records like that so definitely I will need counter/function etc to get these data.
FName Benefittype BenefitPercentage
---------------------------------------- ----------- -----------------
CHARLENE OLIF 33
CHARLENE EADD 33
CHARLENE ELIF 33
TIMOTHY EADD 33
TIMOTHY ELIF 33
BRADLEY ELIF 33
TIMOTHY OLIF 33
BRADLEY OLIF 33
BRADLEY EADD 33
Desired Result in one row:
Charlene OLIF 33, Charlene EADD 33, Charlene ELIF 33, TIMOTHY OLIF 33, TIMOTHY EADD 33, TIMOTHY ELIF 33, BRADLEY OLIF 33, BRADLEY EADD 33, BRADLEY ELIF 33
View 1 Replies
View Related
Jan 10, 2008
Hi Experts ! I want to use maximum feature of SQL
Server 2005 and ASP.Net 2.0 in making Data Access LayerSuggestions will be welcomed .Thank you
RegardsKAMRAN
View 3 Replies
View Related
Jun 4, 2002
Hi Friends ,
When i try to copy one table , say for e.g table x from SQL 6.5 to SQL 7.0 it gives me an error that -
'Insert Error , column 46 ( 'Coloumn_name ',DBTYPE_DBTIMESTAMP), Status 6 :
Data overflow .
Invalid character value for cast specification .'
This column on the source server ( SQL 6.5 ) has datatype 'datetime ' with value 8 and is NULL .
Although there are other columns in this table with datatype as 'datetime' as 8 and NULL , they have been copied successfully to the destination table .Only this specific column is giving an error while copying data .
I am using import export wizard to copy data between servers .
Since i have no experience with this , would like to have a solution from you to this problem. It will be great help for me .
Thank you very much ,
Alisha .
View 1 Replies
View Related
Jul 20, 2005
Hi,If I have a database with collation Sequence X and I change thecollation sequence of database to Collation Sequence Y , do I have tomigrate the data of tables with collation Sequence X to collationSequence Y or SQl server takes care of migrating the data internally.Thanks in advance.-Kalyan
View 1 Replies
View Related
Sep 27, 2007
Hi , I am loading the Data into the Tables with the constraints on and redirecting the error rows into a seperate table is there a way to capture the error rows from a execute sql task by directly loading data without constraints and later adding them with the execute sql task and redirecting them to error table as this approach would make the loads quicker. the approach now that i am using is on a row by row basis ..... and if i drop constraints and load data and then add constraints will this deposit the same error rows as in case of the current approach please send me ur suggestions
View 3 Replies
View Related
Mar 29, 2008
Hi all
I have two data sources - an excel spreadsheet and one access database of Customer details, (quite small in size) and I have to use SQL Server 2005 to Integrate the data and cleanse the data so that I could (in theory) import it into a CRM system (though I won't actually be importing it) as this is for coursework only.
Is there a simple steps to do this is SQL server and guides that I can follow that are relevent? As there are lots of tutorials and I haven't a clue where to start.
many thanks
little_tortilla
View 4 Replies
View Related
Sep 20, 2006
Hi
I have to import data from a number of excel files to corresponding tables in SQL 2005. The excel files are created using excel 4.0. I have created an excel connection manager and provided it with the path of the excel sheet.Next i have added an excel source from the toolbox to the dataflow. I have set the connection manger, data access mode, and the name of the excel sheet (the wizard detects the sheet correctly) in the dialog window i get when i double click the excel source. Every thing goes fine till here. Now when i select the 'columns' in this dialog window or the preview button, i get this error
TITLE: Microsoft Visual Studio------------------------------Error at Data Flow Task [Excel Source [1]]: An OLE DB error has occurred. Error code: 0x80004005.Error at Data Flow Task [Excel Source [1]]: Opening a rowset for "test4$" failed. Check that the object exists in the database.------------------------------ADDITIONAL INFORMATION:Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)------------------------------
Any ideas about why is this happening???
Umer
View 1 Replies
View Related
Jan 23, 2007
Can someone help me with this error, so the page can show the rocords, its works on my PC but not at my host.
I get this error:
Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[OleDbException (0x80040e10): No value given for one or more required parameters.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +267
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +192
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +48
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +106
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +111
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +4
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +141
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
My code is:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="CMS_Default.aspx.vb" Inherits="cmssystem_CMS_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="CM_form" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" DataSourceID="CMSqlDataSource">
</asp:GridView>
<asp:SqlDataSource ID="CMSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:CMConnectionString %>"
ProviderName="<%$ ConnectionStrings:CMConnectionString.ProviderName %>" SelectCommand="SELECT [SiteMainID], [SiteMainIdentity], [SiteMainText] FROM [MainSiteText] ORDER BY [SiteMainID]">
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
View 7 Replies
View Related
Apr 9, 2002
Hello everybody..
I have to copy data from a view of a database to an Excel file .
Is there any way of doing it ? Is there any way to import the data from the views to an Excel format ? We have SQL Server 7.0 .
Any information will be of great help to me .
Thank you very much.
Deepa.
View 2 Replies
View Related
Apr 30, 2015
I have a table in Server A and it has 5 columns. One is Address & ID, CreateDatetime,..
I need to transfer data from this table from Server A to server B for a report pupose. The Address column in this table has some places two address in the table. I am giving ex below
Address
Houston, Dallas
Redmond
Sacramento
New Jersey, New York
I want to avoid the rows where address are two Houston, Dallas & New Jersey, NewYork to the destination table in server B and need to do incremental loads.
how to proceed with this?
The version we are using is Sql Server standard edition 2008r2
View 2 Replies
View Related
Mar 19, 2014
I have three tables EmpIDs,EmpRoles and LatestRoles. I need to write a select Query to get roles of all employees present in EmpIDs table by referring EmpRoles and LatestRoles.
Where I stuck : The condition is first look into table EmpRoles and if it has more than one entry for a particular Employee ID than only need to get the Role from LatestRoles other wise consider the role from EmpRoles .
Example:
Create Table #EmpIDs (
EmplID int
)
Create Table #EmpRoles (
EMPID int,
[Code] ....
Employee ID 2 is having two roles defined in EmpRoles so for EmpID 2 need to fetch Role from LatestRoles table and for remaining ID's need to fetch from EmpRoles .
My Final Output of select query should be like below.
EmpID Role
1 Role1
2 Role2
3 Role1
View 5 Replies
View Related
