How To Check Normalized Tables?
May 7, 2004Hello, everyone:
Some tables werer sent from customer. They ask to check if these tables have been normalized. How to check them? Thanks a lot.
ZYT
ADVERTISEMENT
Constraints Across Normalized Tables
Feb 23, 2007Dear Experts,When I use a single table I can easily use constraints to enforce mybusiness logic, but what do I do when I normalize a single table intomultiple tables.For example, imagine that my initial table has the columns ID, Name,Salary with the constraint that Salary is not NULL. Now imagine thatI break this into two tables, one with ID and Name and another with IDand Salary. I would like to have a constraint that prevents thecreation of a row with (ID,Name) in the first table unless acorresponding row in the second table is also created.I can enforce this logic with triggers, but it looks ugly and isfairly brittle. Is there a better way or is this the dark side ofnormalization?Thanks.
View 6 Replies View RelatedDisplaying Data In Datagrid From A Normalized Set Of Tables
Jul 23, 2007Ok, I'm fairly new to .NET and even newer to the whole database concept. But, don't run away yet, I'm no idiot and I shouldn't have too hard of a time understanding your responses if you're kind of enough to give them. That being said, here's my dilemma:I'm trying to make a database of all the movies I own, the actors in them and the genre (s) they belong to. I have a set of tables that are in the 2NF (I think). I have a movies table, an actors table, a genres table, and two tables called movies_actors and movies_genres with primary-foreign key relationships to pull it all together (e.g. movie_id 1 has two entries in movies_genres, one for Action and one for Drama). My problem arises that when execute my monster query to pull ALL the data on one movie, I get a row returned for every combination of Genres and Actors in a movie. Example:movie_id movie_title comments actor_first actor_last genre_name1 Casino blah blah Robert DeNiro Action1 Casino blah blah Robert DeNiro Drama1 Casino blah blah Joe Pesci Action1 Casino blah blah Joe Pesci Drama And here's the query that produced that:1 SELECT movies.movie_title, movies.comments, actors.actor_first,
2 actors.actor_last, genres.genre_name
3 FROM movies INNER JOIN movies_actors ON movies.movie_id = movies_actors.movie_id
4 INNER JOIN actors ON movies_actors.actor_id = actors.actor_id
5 INNER JOIN movies_genres ON movies_genres.movie_id = movies.movie_id
6 INNER JOIN genres ON movies_genres.genre_id = genres.genre_id
So, I want to put all the actors for one movie into the same cell in the datagrid (same with the genres) and still keep it sortable by actor or genre. Is this possible with the .NET 2.0 datagrid? Do I have some fundamental misunderstanding of how my tables should be structured? Am I just really far off and acting like a n00b?
How-do-I: Add Records (multi-tables) In A Normalized Database
Aug 16, 2007Hi there -
First of all I think of myself as a software developer and by no means a database expert. So I am posting a question here hoping that someone will lead me in the right direction.
I am somewhat following the database diagram from AdventureWorks with my own "normalized" database and have a question on how to update the table(s).
Here is my situation:
Table: Client
ClientID (PK)
Name
...
Table: Address
AddressID (PK)
AddressLine1
AddressLine2
City
...
Table: AddressType
AddressTypeID (PK)
Name
...
Table: ClientAddress
ClientID (PK1)
AddressID(PK2)
...
The tables should be referenced properly, etc... So my question is, when I add a new Client record, how and when do I update the Client table, Address table and ClientAddress table? If my PK on the Client and Address tables is an autoincriment, do I need it before I can update the ClientAddress table? If so, what do I have to look for, or how do I get the newly created ID to update the ClientAddress?
Is this making any sense? - I'm a little lost/confused. So if anyone can provide me links to where I can learn more about stuff like this and/or post some ideas/solutions, I would greatly appreciate it. Also, if I am not clear on my issue, please ask and I will see if I can clarify it more.
Your help is appreciated in advance!
Bob
Denormalized Access Table To Normalized Database TableS
Apr 17, 2006Hello,
I am pretty new to SSIS, so please excuse me if this is a trivial question.
I have a denormalized database table in an Access database that I need to import into several different tables in a SQL 2005 database. You can think of the Access table as a CustomerOrders table. For example customer related information (i.e. CustomerName, CustomerID, etc...) is repeated with each record in the Access table. When this data gets moved to the SQL 2005 database, I need to insert one record for each distinct CustomerName/Customer ID record into a Customers table. I then need to insert and link every "Order" record into an "Orders" table.
I am sure that this is probably a pretty common task, but I have not found any examples or articles explaining this particular situation. What ways can this be done?
I was thinking I need to loop through each DISTINCT Customer record in the Access (source) table and insert a Customer record into the destination database's Customer table. I would then need to iterate through each row of the Access (source) table and "Lookup" the appropriate CustomerID/Key Field and insert an "Order" record.
The Access table contains over 75,000 rows of data. I am looking for the most appropriate way of doing this with SSIS (so that I don't have to write a custom application to do this!). Any help, input, links, articles, etc. is appreciated!!
TIA
-Brian
Crosstab Normalized Data To Non-normalized
Oct 26, 2001I have an allergy table which has a patientid and an allergy id. i would like to create a view(or SQL statement) that will give me a crosstab of a patient and there allergies(like below)
PATID ALLERGY1 ALLERGY2 ALLERGY3 etc
100 MCS DAC004 DAC003
200 MCS DAC004
300 MCS DAC004 DAC003
The patients have from upto 9 allergies(but some may only have one or 2). Is there a way to do this?
Thanks
SQL 2012 :: How To Provide Data In Denormalized Form From Normalized Tables
Apr 14, 2015I have a requirement to provide data in a denormalized form from normalized tables. Working in SSIS.
I have two tables: EmployeeCountry and Country.
EmployeeCountry
EmployeeId (PK)(FK)
CountryId(PK)(FK)
Country
CountryId (PK)
CountryName
There will only be a max of 3 Country entries for each Employee. So I want to select the EmployeeId and get the three CountryIds so it would look like this:
Employee
EmployeeId
CountryId1
CountryId2
CountryId3
DB Design :: How To Copy Data From Existing Table To Normalized Tables
May 20, 2015I normalized the below tables but I am finding it difficult to copy data to the new tables. How do I copy data from existing table to the normalized tables? see the table structure below and other supporting information:
SKU_DATA(SKU,SKU_Description,Department,Buyer) Note: this table already has data in it.
CREATE TABLE SKU_DATA (
SKU
Integer NOT
NULL,
[code].....
The table structure above have two three determinants( SKU,SKU_Description and Buyer). SKU and SKU_Description are candidate keys. Primary key is SKU.
Normalization : SKU_DATA(SKU,SKU_Description, Buyer)
BUYER(Buyer,Department)
How To Check Two Tables?
Dec 20, 2004Hello, everyone:
I imported a table from a database to another one in same server. How to check if two tables have the same data?
Thanks
ZYT
Non Duplicate Check Between Two Tables?
Apr 22, 2005What I am trying to do is this. I have two tables that someone else created and now I have to fix there mess. Both tables have 3 like fields. I want to check and see if the ProjectNumber field from the Artifacts table does not find a match in the Projects table. If no match is found return the record from artifacts table.
Code:
SELECT *
FROM Artifacts INNER JOIN Project ON Artifacts.ProjectNumber = Project.ProjectNumber
WHERE Artifacts.ProjectNumber NOT IN (Project.ProjectNumber);
The next thing I have to do is insert a record of the three fields from the Artifacts table to the Projects table. Any help would be great.
How To Check Which Tables Were Called?
Oct 12, 2005Hello everyone:
I have some nightly jobs that execute stored procedure to call the tables? I want to know which table are called by these stored procedures. Is it possible? Any idea will be appreciated.
Thanks
ZYT
How To Check Two Tables Are Equal
Jul 31, 2007I have two procedures (old and new) that return queries and have to check that for every possible parameter they return the same data. Any hints?
Thanks, Pablo
View Or Tables To Check For Number Of DBs
Aug 24, 2007I am new with SQL and I am Oracle DBA, trying to learn SQL. Any way, in SQL, is there a view or tables that you can run to pull out a list of the databases inside an instance? I don't want to use the GUI though.
thx
Check The Size Of All Tables In My Database
Jun 26, 2007
Hi
I am trying to check the size of each table in my database?
SELECT <TableName> , 'Size in bytes/megabytes' FROM DATABASE
I can't for the lif of me figure out how this is done.
Any help would be greatly appreciated
Kind Regards
Carel Greaves
Check Constraint Across Parent-child Tables
Jul 20, 2005Hi,DDLs and DMLs:create table #job (jobID int identity(1,1) primary key, jobNamevarchar(25) unique not null, jobEndDate dateTime, jobComplete bitdefault(0), check (([JobEndDate] is null and [JobComplete] = 0) OR([JobEndDate] is not null and [JobComplete] = 1)));Q1 with check constraint:sample dagtainsert into #job (jobName)values('first job');transaction Aupdate #jobset jobEndDate = '12/19/2003', JOBCOMPLETE=1where jobID = 3;RESULTSET/STATUS = Successupdate #jobset jobEndDate = NULL, JOBCOMPLETE=0where jobID = 3;RESULTSET/STATUS = Successtransaction Cupdate #jobset jobEndDate = '12/19/2003'where jobID = 3;RESULTSET/STATUS = Failurehow come check constraint can't set a value which is preset in thecheck constraint? If it's the way how it works with MS SQL Server2000, well, IMHO, it's limiting because the above transaction C is avalid one. Or maybe check constraint is not fit for this purpose?Maybe, it doesn't make much sense for me to go into Q2 but I'll try-- create job's child table, taskcreate table #task (taskID int identity(1,1) primary key, taskNamevarchar(25) unique not null, taskEndDate dateTime, taskComplete bitdefault(0), jobID int not null references #job (jobID));-- skip check constraint for taskEndDate and taskComplete for nowNow, the Business Rule says,1) if all tasks are complete then automatically set jobComplete forthe #job table to yes;2) the jobEndDate in the #job table must be >= the last/MaxtaskEndDateI tend to think trigger would slow down data update quite a bit, so,try to stay away for this purpose if possible.Always appreciate your idea.
View 2 Replies View RelatedTransact SQL :: Check No Inserts Occurring In Tables
Sep 14, 2015I want to check that no inserts are occurring in 5 tables that are depending on each other and then drop and create those 5 tables. I have scripts to drop and recreate the tables. How do I check that no inserts are happening in these 5 tables?
Table A
Table B dependant on
Table A
Table C dependant on
Table B
Table D dependant on
Table C
Table E dependant on
Table D
Using A Normalized (vertical) Table
Sep 24, 2007I'm working on a couple projects and I've recently been trying to make everything fully normalized so updates are easier and I'm just wondering if there's a standard way to query and update normalized tables.
For example:
If I have table People with columns ID, FirstName, LastName, Height, Weight, ShoeSize, I can normalize that into two tables. Table People has ID, FirstName and LastName. Table PeopleDetails has PeopleID (FK), Property and Value. That way i can add more properties later right at the presentation layer if I like. Essentially I moved the data from being horizontal to being vertical.
But doing a simple search for people means I have to search the details table and return a LOT more records (one each for Height, Weight and ShoeSize) not to mention any more details I might add later. With a lot of details, it seems like your performance would take a big hit and your code would get really complicated as your looping through a vertical dataset to find the properties you want. Or is there some other standard way of doing that?
I'm just hoping that someone else has solved these problems and there's a standard set of functions out there for selecting and updating this kind of DB structure. Anyone?
Creating A Normalized Database
Apr 12, 2007Hi. I have a project I need to complete and I really don't know the first step I should take. Basically, we have a case management system that is normalized for the most part except for one major flaw: the clients table. Whenever we add a new case, we have to add the customer's name/address/phone etc. all over again. I would like to redo this current setup so we have one table just for clients and another table just for cases, so we don't have all this double entering all the time. Is there an easy way to do this or could someone point me in the right direction? It's on a SQL Server 2000 database. Thanks for your help!
View 6 Replies View RelatedGenerate A Normalized XML Doc From SQL Server
Jul 20, 2005I'm trying to generate a normalized XML document out of SQL serverthat reflects the datastructure of a table.Eg. This is what I would like to get<table name='MtFeedback'><field name="MtFeedbackIy" type="int"/><field name="Title" type="varchar" size="50"/><field name="FirstName" type="varchar" size="50"/><field name="Surname" type="varchar" size="50"/><field name="insertedon" type="datetime"/></table>I have tried two ways of and these are the results. The 1st techniqueis closeselect1 as Tag,null as Parent,'' as [table!1],null as [field!2!table-name],null as [field!2!name],null as [field!2!type],null as [field!2!size]UNION ALLselect2 as Tag,1 as Parent,null as [table!1],i.TABLE_NAME as [field!2!table-name],i.COLUMN_NAME as [field!2!name],i.DATA_TYPE as [field!2!type],i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]frominformation_schema.columns iwheretable_name = 'MtFeedback'FOR XML EXPLICIT<table><field table-name="MtFeedback" name="MtFeedbackIy" type="int"/><field table-name="MtFeedback" name="Title" type="varchar"size="50"/><field table-name="MtFeedback" name="FirstName" type="varchar"size="50"/><field table-name="MtFeedback" name="Surname" type="varchar"size="50"/><field table-name="MtFeedback" name="insertedon" type="datetime"/></table>The 2nd technique fails totally.select1 as Tag,null as Parent,i.TABLE_NAME as [table!1!name],null as [field!2!name],null as [field!2!type],null as [field!2!size]frominformation_schema.columns iUNION ALLselect2 as Tag,1 as Parent,null as [table!1!name],i.COLUMN_NAME as [field!2!name],i.DATA_TYPE as [field!2!type],i.CHARACTER_MAXIMUM_LENGTH as [field!2!size]frominformation_schema.columns iwheretable_name = 'MtFeedback'FOR XML EXPLICIT<table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="MtFeedback"/><table name="categories"/><table name="categories"/><table name="products"/><table name="products"/><table name="products"/><table name="descriptions"/><table name="descriptions"/><table name="descriptions"/><table name="syssegments"/><table name="syssegments"/><table name="syssegments"/><table name="sysconstraints"/><table name="sysconstraints"/><table name="sysconstraints"/><table name="sysconstraints"/><table name="sysconstraints"/><table name="sysconstraints"/><table name="sysconstraints"/><table name="dtproperties"/><table name="dtproperties"/><table name="dtproperties"/><table name="dtproperties"/><table name="dtproperties"/><table name="dtproperties"/><table name="dtproperties"><field name="MtFeedbackIy" type="int"/><field name="Title" type="varchar" size="50"/><field name="FirstName" type="varchar" size="50"/><field name="Surname" type="varchar" size="50"/><field name="insertedon" type="datetime"/></table>Cheers Dave
View 1 Replies View RelatedCheck 2 Tables A Delete Records Based On Common Fi
Aug 8, 2007I'm kinda embarrased, it's been quite awhile since I've played with SQL or Access but here's my brain fart. I need to open an access DB by the name of eHomes. Inside there are two tables AD_IMAGE & PROPERTY. Property is the main table and stores most of the info. It has a field in it called AD_ID and others except for the image file name. The AD_IMAGE table just keeps the image file name and has 3 fields: ID - AD_ID - & Image1. The problem is when the ad is deleted from the property table the AD_IMAGE table record referring to the PROPERTY.AD_ID doen't get deleted. So, I just want the AD_IMAGE table cleaned up to match what's active in the PROPERTY table using the common AD_ID fields in both tables.
Thanks
Onverting A Matrix To A Normalized Format
Feb 12, 2007Jamie writes "I trying to write a SQL solution using embedded and/or dynamic SQL to convert a matrix file to a normalized format."
View 1 Replies View RelatedUnnormalized Source Into Normalized Destination
Feb 28, 2008 Hi
I am new to SSIS. Probaly What I am asking is the simple one. But I dont know. I have a requirement. My Data source is Excel file and Destination is SQL Server . Excel file consist around 10k unnormalized rows which should be loaded to the multiple normalized master and child tables into the destination with the Primary key (identity column) and foreign key constrains. Even sometimes package should check for destination database table for matching Source column (Excel) code with the lookup table code (destination lookup table) and get the Primary key of Lookup table and put it into the destination table. Please help me the way to find the solution for this
Trouble With Query For Poorly Normalized Db
Aug 21, 2007I'm working with a database that is poorly normalized and am doing my best to increase the speed of our queries (changing the database itself is not an option right now). Any help with the following situation would be very much appreciated.
There are two tables involved, described below.
DataTable:
Code Snippet
CodeA char(4),
CodeB char(4),
CodeC char(2),
ColA float,
ColB float,
ColC float,
...
ColZ float
NameTable:
Code Snippet
Abbr char(2),
FullName varchar(50)
In the DataTable, the columns named ColA, ColB, ColC, etc., are described in the NameTable where Abbr would be A, B, C, etc., and FullName would be a "nice" text version describing what's stored in that column. For instance, a record in the NameTable might include Abbr = 'A' and FullName = 'Computer Science', and that means that ColA in the DataTable refers to 'ComputerScience'.
The table I'm trying to optimize the creation of in our ASP.NET application needs to have column headings that could be retrieved like this:
SELECT DISTINCT CodeB, CodeC FROM DataTable WHERE CodeA = @CodeA
(Note that CodeC is really just a different representation of CodeB--think 4-digit year vs. 2-digit year--for any given CodeA.)
The row headings for the table could be retrieved like this:
SELECT FullName FROM NameTable
The cells of the table at any given intersection of a row heading and a column heading can be retrieved like this:
SELECT Col? FROM DataTable WHERE CodeA = @CodeA AND CodeB = @CodeB (this selects a single value)
Where the ? of Col? is determined by the Abbr version of FullName shown in the row heading, and @CodeB is determined by CodeB from the column heading.
Right now this table is generated using a lot of loops in the code of the page that require it to query the database for nearly every cell in the table. Even with this poor database design, there has to be a better way than that. I'm hoping there is a way to create a stored procedure that uses temporary tables and joins, or views, or something like that to create the table on the SQL Server and just send the data to the ASP.NET page in a form that can be directly databound to a GridView.
Thanks in advance for any help!
-Mathminded
SQL 2012 :: Check Size Of Database After Implementing Data Compression Across All Tables
Dec 4, 2014I found it pretty interesting. I checked the size of a database, before implementing database compression across all the user tables in a database. And Post implementation of compression too I checked the size of the database.
I did not find any difference. But if I expand the table and check propetires->storage and I can see that PAGE compression is implemented across all the tables, but no compaction in the size of the db. It still remains the same.
Split Wide, Denormalized Table Into Normalized Structure
Aug 27, 2002Thanks for reading.
This is pretty long, hopefully it isn't rambling.
I'm building a system that imports data from several source, Excel files, text files, Access databases, etc. using DTS. The entire process revolved around MS SQL Server, by the way.
I figured I would create denormalized tables that mirror the Excel and flat files, for example, in structure, import data to those, clean up and remove duplicates there, then break those out into my normalized table structure later.
Now I've finished the importing part (though this is going to happen once a week) and I'm onto breaking up the denormalized tables.
I'm hesitating because I'm not sure I've made the best decisions in terms of process, etc.
I've decided to use cursors to loop over the denormalized tables and use batch insert statements to push data out to the appropriate tables.
Any comments? Suggestions? All is welcome.
I'm specifically interested in hearing back on the way I've set up the intermediate, denormalized tables and how I'm breaking them up using cursors (step 2 of the process below). Still, all comments are welcome. As are suggestions for further reading.
Thanks again...
simplified example
(my denormalized tables are 20 - 30 colums wide)
denormalized table:
===================
name, address, city, state, cellphone, homephone
normalized tables:
==================
tblPerson [PK_person, name, age, height, weight]
tblAddress [PK_address, FK_person, street, city, state, zip, addressType]
tblContact [PK_contact, FK_person, data, contactType]
I'm breaking up the denormalized tables like this (*UNTESTED*):
=================================================
DECLARE @vars.... (one for each column in my normalized table structure, matching size and type)
DECLARE myCursor CURSOR
FAST_FORWARD FOR
SELECT name, address, city, state, cellphone, homephone
FROM _DNT_myWideTable
INTO
WHILE @@Fetch_Status = 0
BEGIN
-- grab the next row from the wide table
FETCH NEXT FROM myCursor
INTO @name, @address, @city, @state, @cellphone, @homephone
-- create the person first and get the ID with @@IDENTITY
INSERT INTO tblPerson (name) VALUES (@name)
SET @personID = @@IDENTITY
-- use that ID to coordinate inserts across other tables
INSERT INTO tblAddress (FK_person, address, city, state, addressType)
VALUES(@person, @address, @city, @state, 'HOME')
INSERT INTO tblContact (FK_person, data, contactType)
VALUES(@person, @cellphone, 'CELLPHONE')
INSERT INTO tblContact (FK_person, data, contactType)
VALUES(@person, @homephone, 'HOMEPHONE')
END
Review Of DB Design - Normalized, Contraints, Foreign Keys, Etc.
Jul 2, 2004First of all, this is my initial thread here on dbforums. I come from the land of Broadband Reports and would like to say, Hello fellow DB enthusiasts. :)
I'm not a novice to relational databases (Access MDBs), but new to implementing a db via SQL SERVER (2000 in this case) and using Access Data Projects.
My partial db schema is as follows:
participants
---DID (pk) char(1)
---LID (fk - schools) char(4)
---studentLast varchar(50)
---studentFirst varchar(25)
Sample Data would be
010191M001 | 5671 | SPARKS | JONATHAN
030495F283 | 5671 | DYLAN | CYNTHIA
=====================================
enrollhist (insert/update trigger for enrollactive)
---EID (pk - autonumber) bigint(8)
---EMID (fk - enrollmode) int(4)
---DID (fk - participants) char(10)
---LID (fk - schools) char(4)
---enrollactive bit(1)
Sample Data would be
38173 | 4 | 030495F283 | 9003 | 0
38266 | 3 | 010191M001 | 5671 | 0
39022 | 6 | 030495F283 | 9003 | 0
39036 | 5 | 030495F283 | 9003 | 0
39044 | 4 | 030495F283 | 5671 | 1
39117 | 4 | 010191M001 | 5671 | 1
=====================================
enrollmode
---EMID (pk) int(4)
---mode varchar(25)
Sample Data would be
1 | RECEIVED
2 | WAITING
3 | PENDING
4 | ENROLLED
5 | DROPPED
6 | TRANSFERRED
10 | ORPHANED
11 | DENIED
=====================================
schools
---LID (pk) varchar(4)
---CTID (fk - caltracks) char(1)
---AID (fk - agencies) char(1)
---SDID (fk - schooldist) char(1)
---COID (fk - countydist) char(1)
---sitename varchar(25)
---sitetitle varchar(75)
Sample Data would be
5671 | 3 | 2 | 1 | 4 | ASCOT | ASCOT AVENUE
9003 | 2 | 1 | 4 | 1 | ROWAN | ROWAN AVENUE
2865 | 1 | 3 | 2 | 3 | BRIGHT | BIRDELEE BRIGHT
=====================================
caltracks
---CTID (pk) char(1)
---legend char(4)
---trktitle varchar(15)
---trkcnt int(4)
Sample Data would be
1 | 9030 | 90/30 | 4
2 | CON6 | CONCEPT-6 | 3
3 | SNGL | SINGLE TRACK | 1
=====================================
agencies
---AID (pk) char(1)
---legend varchar(4)
---agencytitle varvhar(50)
Sample Data would be
1 | CRYS | CRYSTAL STAIRS
2 | MAOF | MEXICAN AMERICAN FOUNDATION
3 | PATH | PATHWAYS
4 | CCRC | CHILD CARE RESOURCE CENTER
5 | CHSC | CHILDREN'S HOME SOCIETY OF CALIFORNIA
==========================================
THE REMAINING "FKs" FROM SCHOOL ARE SIMILAR, as is other tables and their relationships. The design of the foreign keys were made using sql and the keyword "REFERENCES" and "FOREIGN KEY."
My questions are: :confused:
(1) Is the use of FK as a Constraint any different than using an INDEX and how?
(2) Should I Alter the Tables to include CASCADING Up/Down?
(3) Are the use of CHARs Ok for the Keys?
(4) Have I over/under-normalized any of the relationships?
Stored Proc To Copy Unnormalized To Normalized Table
Jul 20, 2005I have a "source" table that is being populated by a DTS bulk importof a text file. I need to scrub the source table after the importstep by running appropriate stored proc(s) to copy the source data to2 normalized tables. The problem is that table "Companies" needs tobe populated first in order to generate the Identity ID and then usethat as the foreign key in the other table.Here is the DDL:CREATE TABLE [dbo].[OriginalList] ([FirstName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LastName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Company] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Addr1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Companies] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[CompanyLocations] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[CompanyID] [int] NOT NULL ,[Addr1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Phone] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOThis is the stored proc I have at this time that does NOT work. Ituses the last Company insert for all the CompanyLocations which is notcorrect.CREATE PROCEDURE DataScrubSP ASBegin Transactioninsert Companies (Name) select Company from OriginalListIF @@Error <> 0GOTO ErrorHandlerdeclare @COID intselect @COID=@@identityinsert CompanyLocations (CompanyID, Addr1, City, State, Zip) select@COID, Addr1, City, State, Zip from OriginalListIF @@Error <> 0GOTO ErrorHandlerCOMMIT TRANSACTIONErrorHandler:IF @@TRANCOUNT > 0ROLLBACK TRANSACTIONRETURNGOThanks for any help.Alex.
View 3 Replies View RelatedSQL Server Admin 2014 :: SSMS - Disable Check For Memory Optimized Tables?
Oct 2, 2014I have the following setup:
- An MSSQL 2014 Standard server that houses multiple small databases (in excess of a hundred).
- These databases are frequently dropped and restored by an application that uses this SQL Server.
- There is a business need for this setup at this time, so I can't get away from it. Therefore answers like "don't have so many small databases that are frequently dropped and restored" would be somewhat unuseful
This is the problem I have:
- When I connect SSMS 2014 to the server and expand the "Databases" node, it takes forever to display. In comparison, SSMS 2008 connected to SQL 2008R2 server with the same number of databases displays the Databases tree very quickly.
I ran a trace to see what exactly SSMS 2014 is doing. When the "Databases" node is expanded, it runs a query that checks each database for Memory-Optimized Tables (new and wonderful feature of SQL 2014 for sure, but I'm not using it, at least yet). Naturally, when you have to loop through over a hundred DBs, it takes time. Worse yet, if one of these DBs is in process of being restored, the query sits and waits to time out before proceeding to the next DB. Sometimes this causes outright timeouts. Here is the query:
use [MyDatabase]
SELECT
ISNULL((select top 1 1 from sys.filegroups FG where FG.[type] = 'FX'), 0) AS [HasMemoryOptimizedObjects]
To be sure, this is NOT a SQL Server performance issue. This server processes a rather heavy workload and has been doing so for over a month, and the workload completes within expected time limits or better. Even so I've done some basic performance measuring, and the server itself is quite all right.
Moreover, if I connect SSMS 2008 to it, I get an error message (Index out of bounds or somesuch), but SSMS 2008 does connect, and displays the Databases tree much faster than SSMS 2014.
I'd like to turn off the option to check for Memory Optimized Objects altogether, as I'm not using the feature.
Writing Insert And Update Stored Procedures For Normalized Schemas?
May 25, 2006I have a database schema that has an Address table used to store addresses for different entities such as Customers and Employees. I want to reuse the same Address record between different Customers and Employees without duplicating any address information. I'm not sure what the best approach might be.
Should have I have seperate stored procedures on the Address table that update and insert new addresses, where each Address record remains immutable once created? (So the update stored procedure actually creates a new Address record if the data changes). These stored procedures would then be invoked by business logic and used in tandem with stored procedures that act on Customers and Employees to ensure that no address records are duplicated.
Or should I create a view on a Customer joined with Address, and similarily with Employee and Address, and have stored procedures that act on these views and ensure that no Address records are duplicated. Should I use instead of triggers to override the behavior of insert and update on the view to achieve these?
I'm rather lost as to what direction I should take. Any help would be much appreciated, thanks!
SQL Server 2012 :: Converting Large Excel Spreadsheet To Normalized Data
Aug 7, 2014I have a large excel spreadsheet created by finance user that contains several decades worth of sales data.
Here is a small sample:
Guest Count
Unit ID1/2/2011 1/9/2011
3 0
7 0
8 0
90 0
151696 1202
222769 1914
232704 2110
250 0
282838 1882
331089 691
363581 3064
371469 1062
I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.
The desired output:
Unit IDDate Guest Count
31/2/2011 NULL
71/2/2011 NULL
81/2/2011 NULL
91/2/2011 0
151/2/2011 1696
and so on ......
The spreadsheet has 2900 columns and 3500 rows so performance is definitely a consideration as well.
Data Access :: How To Check All Connection Automatically During Routine Check By Using Batch File
May 20, 2015I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies View RelatedHelp W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data
Mar 29, 2008Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies View RelatedHelp Please (Check File Exists/ Archive File/ Check If File Empty)
Mar 10, 2008
Hello World,
I'm new to SSIS and would like a little assistance getting started, if possible...
Here is what I want to do:
Check if file exist (C:DTS UpgradeFilexxx.txt) --->
Archive file (C:DTS UpgradeArchive) --->
Check if file has data (true or false)
AND/OR
If there are any good website that have good direction, let me know
Thanks in advance for your help!!!