Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Normalizing data.

Author  Topic 

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2013-09-23 : 14:17:54
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

52326 Posts

Posted - 2013-09-23 : 14:27:17
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

52326 Posts

Posted - 2013-09-23 : 14:27:57
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

651 Posts

Posted - 2013-09-23 : 14:33:03
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

52326 Posts

Posted - 2013-09-25 : 07:49:01
welcome

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

- Advertisement -