SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Normalizing data.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 09/23/2013 :  14:17:54  Show Profile  Reply with Quote
I'm having to re-normalize(?) data already in a SQL table so that it can have more logical relationships.

Table that needs changes is called "Databases". There are currently four columns. A primary Key called "AutoID" (autnumbers for each new row), "Servername" a varchar(50), and "Databases" which currently contains from one to ten database names. There is a fourth empty column called "FKAutoID" which will be populated when i fix this.

The AutoID field relates to each servername once. There is a "Servers" table which has the same servernames and autoid values.

I need to break out these cells which have more than one database name in them and create a new row for each. For instance, the Servername "Pens16168" may have five user databases associated with it, all crammed into one cell and seperated by a space. So, in that case, i need to create a total of FIVE rows each with a unique AutoID value, one for each database in that cell, and with ONE FKAutoID value which relates back to the AutoID value in the Servers table in that same database.

If the easier path is to shove this new data into a new table as it's parsed out, i'm fine with that. I know there is going to be an "insert into" statement with a substring based on a patindex or a charindex, but i'm stuck.

any help is appreciated.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/23/2013 :  14:27:17  Show Profile  Reply with Quote
something like

INSERT INTO Databases (Servername,databases,FKAutoID)
SELECT d.Servername,f.val,s.AutoID
FROM databases d
CROSS APPLY dbo.ParseValues(d.databases,' ')f
INNER JOIN Servers s
On s.Servername = d.Servername
WHERE f.ID > 1


UPDATE d
SET d.Databases = f.Val,
d.FKAutoID = s.AutoID
FROM databases d
CROSS APPLY dbo.ParseValues(d.databases,' ')f
INNER JOIN Servers s
On s.Servername = d.Servername
WHERE f.ID = 1 



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/23/2013 :  14:27:57  Show Profile  Reply with Quote
ParseValues UDF can be found in below link

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

WJHamel
Aged Yak Warrior

USA
644 Posts

Posted - 09/23/2013 :  14:33:03  Show Profile  Reply with Quote
Yep, i forgot i had that "parsevalues" function. It's saved my butt before. Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/25/2013 :  07:49:01  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000