Set Primary Key When Normalizing Data?

Apr 27, 2006

Greetings all,

I have created an SSIS package that takes data from a very large table (301 columns) and puts it in a new database in smaller tables. I am using views to control what data goes to the new tables. I also specified that it drop the destination table and recreate it prior to copying the data. The reason for this is so that old data removed from the larger database will get removed from the normalized databases.

I have 2 things I am trying to figure out..

1. I would like to have the package set a specific row in each new table to be the primary key (this will allow us to use relationships when querying the data).

2. I decided I wanted to sort the data as it copies. I am using the BI Visual Studio for my editing. In the Data Flow view I cannot seem to disconnect the output from the Source block so I can connect it to the Sort block and then feed that to the output block. What am I missing here?

Thanks

View 7 Replies

Primary Key, Normalizing?

Feb 24, 2004

View 6 Replies View Related

Normalizing The Data

Jun 9, 2006

Hi,

I have a table like this:

Col1 First_Year Last_Year
a 1990 1993

I want this data to be converted to
a 1990
a 1991
a 1992
a 1993

Is there any simple way to do it in SSIS without using Script Component?

Thx

View 3 Replies View Related

Normalizing Flat Row Of Data

Nov 7, 2005

I have a view with patient data. It looks like below

patid date pulmdc pulmstatus endodc endostatus
100 4/1/05 10 Good null null
100 5/1/05 10 Good 12 Poor

I want to create sql which by each patient, by date, by these four fields ,get

patid 4/1/05 pulmdc-10 pulmstatus-good
patid 5/1/05 pulmdc-1 pulmstatus-good
patid 5/1/05 endodc-12 endostatus-poor

View 1 Replies View Related

Normalizing Flat Data / Extract One Of Each Type

Nov 6, 2007

I'm new to SSIS and have run into a problem I'm hoping someone can help me with.

Basically, I have a flat file that looks something like:

ID,Type,Description,Results
1,Test1,This is a test,5
2,Test1,This is also a 1 test,7
3,Test1,This is also a 1 test,13
4,Test2,This is a second test,14
5,Test2,This is also a second test,18

I'm trying to normalize the data by extracting out individual rows that have the same "Type" column value. So what I want is to extract each unique type and description into a separate table. This would give me two new rows, one for a type of Test1, and one for a type of Test2, with the descriptions. Does this make sense? Then I could relate the individual results to these test types. In my scenario, I don't care which description is used; I just want to take the first description that shows up with the associated "Type."

Does anyone have any idea of how I could go about doing this? I could pull out all unique "Types" from the rows with the Aggregate transformation, but I'm trying to figure out how to get the description that goes along with it.

Thanks,

Brian

View 1 Replies View Related

SQL Server 2008 :: Normalizing Data Prior To Migration (Update String To Remove Special Characters)

Aug 21, 2015

I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.

I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.

There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.

Special Characters are defined as #%&*:<>?/{}|~ and ..

While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.

Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16

The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.

In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.

My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.

I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.

View 3 Replies View Related

Normalizing My Database

Nov 8, 2006

Please i have created some tables Delivary with this columns (DelivaryId,DelivaryNo,QtyRecieved,DelivaryDate,ProductId) and Product with this columns (ProductId,ProductCode,ProductName,ProductPrice) as you can see the product table keeps record of products whlie the delivary table keeps record of stock supplied. I will like to create another table that will keep record of stock sold out (Invoice Table) based on the qty recieved from the delivaries table
Please help

View 6 Replies View Related

I Need Help Normalizing This Table.

Aug 10, 2005

So I'm creating an administrative back end for a site that's already been created, and whoever made the tables the site uses didn't know much about database design. So I need to normalize this table of Links so it can be easier to have someone make changes and updates to it, but then I need to put all my normalized tables back together to create a View exactly like the old table which the old site can select from. Basically the stipulation is I can't change the code for the old site so I have to make it think it's still selecting from the same table with the same type of parameters. Is it worth doing all this? Or should I just tough it out with this really ugly table?Here's the table: and here's the site that uses this table:http://waahp.byu.edu/links.aspThanks!~Cattrah~

View 3 Replies View Related

Normalizing Using Sql Commands

Aug 28, 2006

Hi

Please can someone point me in the direction, i built a very badly designed database consisting of only one huge table when i first started databases, since learning about normalization i have designed and set up a new database which consists of many more tables instead of just the one. My question is where do i start in transfering the data from the old single tabled database to my new multi-tabled database?

I have MS SQL server 2005 managment studio if that helps, but want to transfer around 200,000 rows of data into the new database. Both new and old databases are on the same server.

thanks in advance

View 11 Replies View Related

De-normalizing Query

Jul 13, 2006

I have this table...CREATE TABLE #Test (ID char(1), Seq int, Ch char(1))INSERT #Test SELECT 'A',1,'A'INSERT #Test SELECT 'A',2,'B'INSERT #Test SELECT 'A',3,'C'INSERT #Test SELECT 'B',1,'D'INSERT #Test SELECT 'B',2,'E'INSERT #Test SELECT 'B',3,'F'INSERT #Test SELECT 'B',4,'G'....and am searching for this query....SELECT ID, Pattern=...?? FROM #Test....??....to give this result, where Pattern is the ordered concatenation ofCh for each ID:ID PatternA ABCB DEFGThanks for any help!Jim

View 2 Replies View Related

Normalizing A Crosstab

Aug 24, 2006

I re-designed a predecessor's database so that it is more properlynormalized. Now, I must migrate the data from the legacy system intothe new one. The problem is that one of the tables is a CROSSTABTABLE. Yes, the actual table is laid out in a cross-tabular fashion.What is a good approach for moving that data into normalized tables?This is the original table:CREATE TABLE [dbo].[Sensitivities]([Lab ID#] [int] NULL,[Organism name] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[Source] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[BACITRACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CEPHALOTHIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CHLORAMPHENICOL] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CLINDAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ERYTHROMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[SULFISOXAZOLE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[NEOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OXACILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[PENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TETRACYCLINE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[TOBRAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[VANCOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TRIMETHOPRIM] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CIPROFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[AMIKACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[AMPICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CARBENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CEFTAZIDIME] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GENTAMICIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[POLYMYXIN B] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MOXIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[GATIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[SENSI NOTE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]

View 5 Replies View Related

Normalizing Address Information...

Dec 27, 2003

THE LAYOUT:
I have two tables: "Applicant_T" and "StreetSuffix_T"

The "Applicant_T" table contains fields for the applicant's current address, previous address and employer address. Each address is broken up into parts (i.e., street number, street name, street suffix, etc.). For this discussion, I will focus on the street suffix. For each of the addresses, I have a street suffix field as follows:

[Applicant_T]
CurrSuffix
PrevSuffix
EmpSuffix

The "StreetSuffix_T" table contains the postal service approved street suffix names. There are two fields as follows:

[StreetSuffix_T]
SuffixID <-----this is the primary key
Name

For each of the addresses in the Applicant_T table, I input the SuffixID of the StreetSuffix_T table.

THE PROBLEM:
I have never created a view that would require the primary key of one table to be associated with multiple fields of another table (i.e., SuffixID-->CurrSuffix, SuffixID-->PrevSuffix, SuffixID-->EmpSuffix). I want to create a view of the Applicant_T table that will show the suffix name from the StreetSuffix_T table for each of the suffix fields in the Applicant_T table. How is this done?

View 6 Replies View Related

Need Help Normalizing Multivalue Column

Jan 23, 2006

Hi!

I have a table with the following columns:

account_nr, account_totaling_members, account_type

the account_totaling_members column contains a pipe sperated list of accounts in a varchar: "1001|1002|1003"

I need to normalize this so that i get records like:

"10", "1001", "sum"

"10", "1001", "sum"

"10", "1002", "sum"

..and so forth

Does anyone have any idea how to accomplish this?

View 3 Replies View Related

How To Populate Foreign Key In Normalizing Import?

Jun 21, 2006

I am copying data from one denormalized table to a COUPLE of normalized ones.
I am using multicast, following advices from the forum.

The problem I have is that the two destination tables (A and B) are sharing a foreign key relationship.Filling in A is no problem, but when I want to fill in B, I don't know how to populate its foreign key, since the multicast doesn't know the corresponding primary key in table A.

View 9 Replies View Related

SQL Server 2012 :: Normalizing A Column Containing Lists

Aug 20, 2015

CREATE TABLE CATEGORIES(CATEGORYID VARCHAR(10), CATEGORYLIST VARCHAR(200))

INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1000', 'S01:S03, S09:S20, S22:S24')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1001', 'S11:S12')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1002', 'S30:S32, S34:S35, S60')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1003', 'S40')

The CATEGORYLIST strings are composed of value ranges separated by a colon (:) and multiple value ranges separated by a comma.

The results I need are:

CATEGORYID STARTRANGE ENDRANGE
1000 S01 S03
1000 S09 S20
1000 S22 S24
1001 S11 S12
1002 S30 S32
1002 S34 S35
1002 S60 S60
1003 S40 S40

I have tried taking the original data and parsing it out as an XML file. Is there a less cumbersome way to do this in TSQL?

View 1 Replies View Related

DB Design :: Normalizing Personal Contact Information

May 22, 2015

I have a large data set with 10s of millions of rows of contact information. The data is in CSV format and contains 48 columns of information (First name, MI, last name, 4 part address, 10+ demographic points, etc.) and I'm struggling with how I should design the database and normalize this data, or if I should normalize this data.

My 2 thoughts for design were either:

Break the columns into logical categorical tables (i.e. BasicContactInfo, Demographics, Financials, Interests, etc.) Keep the entire row in one table, and pull out the "Objects" into another table (i.e. ContactInformation, States, ZIPCodes, EmployementStatus, EthnicityCodes, etc.)

The data will be immutable for the most part, and when I get new data, I'll just create a new database and replace the old one.

The reason I like option 1 is because it makes importing easier, since I can just insert the appropriate columns from each row into the appropriate tables. Option number 2 feels like it would be faster to get metrics on the data, like how many contacts live in which states, or what is the total number of unique occupations in the data set. Plus I'll be able to make relationships between the tables, like which state is tied to which zipcode, which city is tied with which county, etc. Importing that data might be more tricky, since I don't think SQL Bulk Copy will allow for inserting into normalized tables like that.

The primary use for this data is to allow our sales force to create custom lists of contact information based on a faceted search page. The sales person would create the filter, and then I will provide them with the resulting data so they can start making business contacts. Search performance needs to be good. Insert, update, and deletes won't happen once the data has been imported.

What should I look for in designing this database? Any good articles on designing tables around wide data sets like my contact information?

View 6 Replies View Related

Normalizing Comma Separated String To Multiple Records

Oct 17, 2012

I need to normalise comma separated strings of tags (SQL Server 2008 R2).

E.g. (1, 'abc, DEF, xyzrpt') should become
(1, 'abc')
(1, 'DEF')
(1, 'xyzrpt')

I have written a procedure in T-SQL that can handle this. But it is slow and it would be better if the solution was available as a view, even a slow view would be better.

Most solutions I found go the way round: from (1, 'abc'), (1, 'DEF') and (1, 'xyzrpt'), generate (1, 'abc, DEF, xyzrpt').

If memory serves, it used "FOR XML PATH". But it's been a while and I may be totally wrong.

View 2 Replies View Related

SQL Server 2012 :: Formatting XML Output - Avoid Normalizing Structure On Client

May 28, 2015

I have a script that resolve's data into xml like this, ex:

<root>
<title>A</title>
<id>1</id>
<nodes>
<node>
<id>2</id>
<title>A.1</title>
</node>
</nodes>
</root>

And works perfectly, but ... how to make sure every item has an element "nodes" ? The case here is for the child leafs obviously. This, because on the client i have to inject this element "nodes" on a json version of this xml, and just wanted to avoid normalizing the structure on the client.

For the root I am using

FOR XML PATH('root'),TYPE; and for the hierarchy that follows
FOR XML RAW ('node'), root('nodes'), ELEMENTS

View 0 Replies View Related

How To Get Data From Primary

Nov 1, 2007

Hi Guys,

We have three servers.
Server A - Primary
Server B - Mirror
Server C - Stand Alone (also used for reporting, but, not using SQL Reporting).
All the three servers are interconnected using linked servers.

Heres the brief overview of my problem:

A certain procedure requires me to get live data from the 'Primary' to Server C on regular basis. This procedure will be scheduled to run once a day on Server C.

Heres the code:

create procedure sp_getDataFromPrimary
as

--First I check if Server A is primary
if exists( select * from [serverA].master.sys.database_mirroring where name = 'database1' and
mirroring_role_desc = 'primary')

begin

insert into [serverC].[database1].[dbo].[table1]
select * from [serverA].[database1].[dbo].[table1]
end
else

begin

insert into [serverC].[database1].[dbo].[table1]
select * from [serverB].[database1].[dbo].[table1]
end

go

When I try to create the above procedure, I get an error message similar to 'cannot access
[server B].[database1].[dbo].[table1] as the database is being mirrored. I tried to use variablesfor the server, database and tables, but, no avail -- I still get the same error.

My qn now is : How can I make this work. If you think my approach is faulty, pls, suggest any other.

Eagerly awaiting for your response.

Thankyou.

p.s: Cannot use SSIS for it will complicate the other additional processing that needs to be done.

View 7 Replies View Related

Primary Key Data Type

Dec 11, 2006

i have a employee table with various columns....
here i have emp_code as my primary key

now my concern is which data type is to be associated with the primary key(emp_code)

emp_code int not null,
OR
emp_code varchar not null,

wht i understand is to go for the simpler one....i.e. int, since it would be easy to deal with int data

now my basic question here is while creating tables which data type is to be assigned to emp_code (int or varchar) and on wht basis.......since both is possible...

help appreciated

cheers

View 1 Replies View Related

Exporting Data And Keep The Primary Key

Jan 5, 2006

Hi all,

I am rather fresh to SQL2000, so...

When I export data from database A tot database B and I look at the copied tables in database B, all the primary keys are gone and the field is now a "normal" field ?

How can I keep the primary key in the copied tables ?

Help is appreciated, Ger.

View 4 Replies View Related

How To Get Data From Primary Server

Nov 1, 2007

Hi Guys,

We have three servers.

Server A - Primary

Server B - Mirror

Server C - Stand Alone (also used for reporting, but, not using SQL Reporting).

All the three servers are interconnected using linked servers.

Here��s the brief overview of my problem:

A certain procedure requires me to get live data from the 'Primary' to Server C on regular basis. This procedure will be scheduled to run once a day on Server C.

Here��s the code:

create procedure sp_getDataFromPrimary

as

--First I check if Server A is primary

if exists( select * from [serverA].master.sys.database_mirroring where name = 'database1' and

mirroring_role_desc = 'primary')

begin

insert into [serverC].[database1].[dbo].[table1]

select * from [serverA].[database1].[dbo].[table1]

end

else

begin

insert into [serverC].[database1].[dbo].[table1]

select * from [serverB].[database1].[dbo].[table1]

end

go

When I try to create the above procedure, I get an error message similar to 'cannot access

[server B].[database1].[dbo].[table1] as the database is being mirrored. I tried to use variables for the server, database and tables, but, no avail -- I still get the same error.

My qn now is : How can I make this work. If you think my approach is faulty, pls, suggest any other.

Eagerly awaiting for your response.

Thank you.

p.s: Cannot use SSIS for it will complicate the other additional processing that needs to be done.

View 8 Replies View Related

INSERT Data Into Table That Maybe Have That Primary Key Already

May 12, 2007

Hi, I'm not user to inserting data into databases, usually I just read the data. So I think my problem might be pretty common.I have a table of longitudes, latitudes, city names, and country names. I set the primary key to be the columns longitude and latitude. I have a method that generates the user's location and the mentioned data. So I want to only insert the new data into the database if it is new and unique. currently if the same user goes to my site, it inserts the data fine the first time and then throws and error the second time because it is inserting duplicate primary key information. Do I need to query the database to see if the data record already exists? or is there a way to insert the record only if it is "new"?? Thanks for the help!!

View 2 Replies View Related

Delete Data Which Is A Primary And Foreign Key

May 26, 2008

Hi,
How do I delete data which is a Foreign Key in another table?
For example;
string query = "DELETE * from user_details WHERE user_ID = '" +userID.Text+ "';
The user_ID is the Primary Key in the user_details table and also a Foreign Key in other tables.
Thank you. (:

View 4 Replies View Related

Composite Primary Key Data Type

Jan 2, 2001

hi, can I have a composit primary key which have more than one data type. for instance job_id int,job_type varchar(20)... thanks

Al

View 1 Replies View Related

Deleting Data From Primary Table

Mar 20, 2008

Hi Everybody,

Kindly let me know if there is a way of deleting data from primary table without deleting data from its corresponding foreign key table.

Thanks & Regards

View 2 Replies View Related

Primary Data File Group Is Full

Dec 19, 2007

I got below error :

Could not find allocate space for object 'tablename' in DB, because the primary file group is full

Upon investigation I found :

datafile is reached it's maximum size
DB Size space allowcated 197 MB, space free 20.69 MB
DB mdf 194880 KB, ndf 6272 KB

It's production server, please let me know how to proceed now?

View 6 Replies View Related

SQL Server Database Primary Data File

Aug 8, 2007

Hi Everone,
I need help on how to produce a SQL Server Database Primary Data File, i have read the Visual C# 2006 book by Deitel and Deitel unfortunately the book does not say how to produce the forementioned data file. The software that i currently have to help me produce this data file is Visual C#, J#, Web Development, SQL Server 2005 and Office 2007.

I have a good understanding of databases from studying SQL and Oracle but i am not sure which is the best software to use to produce the Primary Data File, ideally i would like to use Access from my Office 2007 package.

Many thanks for your help in resolving this problem.

View 4 Replies View Related

Backing Up Online Log After Loss Of Primary Data File

May 24, 2004

My question is this:

Is there anyway to backup the current online log after complete loss of the current correponding datafile?

Example:

(2) Logical Disk Volumes

Disk 1 (D:) contains pubs_data.mdf
Disk 2 (E:) contains pubs_log.ldf

Disk 1 becomes corrupt and goes offline leaving the the database pubs in a suspect state. Is backup of the current online log pubs_log.ldf possible? If a backup of the log is not possible are there any other restoration methods that can be used to bring this database back online, rescuing as much information as possible from the current online log pubs_log.ldf.

Thanks,
P

View 1 Replies View Related

How Do I Restore Sql Server Database Primary Data Back .mdf

Feb 12, 2007

Hello guys
My server crashed but luckily I was not able to get back my files with help of recovery software.
Now, all I have from the database are just sql server database primary data .mdf and sql server databaseTransaction Log Files .ldf. I need to restore these data back to sql server.

Please could someone tell me how to restore these two file types back to my sql sever 2007 database?
Thanks
netboy

View 3 Replies View Related

Sql Table Data Stroring Based On Varchar Primary Key

Jan 17, 2008

Hi,

I have a Users table that I use for membership. I am using username varchar(30) as the primary key for this table since username will always be unique.

The question I have is regarding how SQL Server actually stores data:

I see that when I add users, they are always stored alphabetically sorted on username.

I was expecting that all users will appear on the users table in the order they were added.

Example: I have 3 users (john, jonah, wilson). Now I added 4 user with username='bob'

If I execute select * from users, it returns me (bob, john, jonah, wilson). Look bob has become the first row of the table.

My question: Is Sql server moving 3 older rows to make room for 'bob' and it is also rebuilding part of the index due this new username 'bob'?

If this is the case, then it will have big impact if I have 100K users and I add one user that becomes firstrow. In that case 99,999 rows will have to move.

Bottom line, insert, delete will be very expensive.

I know sql server keeps data physically sorted on PK. But I am concerned here since rows are losing the order in which they were inserted.

Thanks

View 2 Replies View Related

SQL 2012 :: Auto ID Primary Key Specified As Int And Table Data Is Deleted Periodically?

Jun 25, 2015

I have a table that has a primary key that is auto incremented by 1. This table's data is cleared out periodically and as data gets added the auto id primary key continues to increase in numeric value. Once the data is cleared from the table the auto id names could be used again(the eventId is not stored) Currently the eventID is at 26,581,399. I know the maximum int value is 2,147,483,647.

How should I handle this? or rebuild the table every time the data is cleared(problematically)?

View 3 Replies View Related

SQL 2012 :: Insert Data Onto Tables Having Primary And Foreign Key Relations?

Oct 31, 2015

Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?

For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.

View 3 Replies View Related