Data conversion - MS SQL Server 2005

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

SQL Server 2005 Forums

Transact-SQL (2005)

Data conversion - MS SQL Server 2005

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2010-04-08 : 08:52:06

Question for the SQL gurus....

I'm researching a Data Conversion.

I've included the Table DDL and some sample Data (current data and expected results data)

The PATH column is the Primary Key.

Example Contents of Path column

templatedata\Article\ColumbiaArticle\data\2004-09_Spalding_en

Need to change to

templatedata\Article\ColumbiaArticle\data\en\2004-09_Spalding

Table DDL

Create table dbo.Article (
    id                             int                            identity,
    IW_State                       VARCHAR(255)                   not null,
    path                           VARCHAR(255)                   not null,
    contentArea                    CHAR(10)                       not null,
    homepage                       CHAR(5)                        null,
    title                          NVARCHAR(400)                  null,
    summary                        NVARCHAR(1000)                 null,
    keywords                       NVARCHAR(50)                   not null,
    author                         NVARCHAR(50)                   null,
    type                           CHAR(10)                       not null,
    subArea                        CHAR(10)                       null,
    publishDate                    datetime                       not null,
    expireDate                     datetime                       not null,
    articleLanguage                CHAR(5)                        not null,
    indexImage                     VARCHAR(255)                   null,
    eventStartDate                 datetime                       null,
    eventEndDate                   datetime                       null,
    eventLocation                  NVARCHAR(50)                   null,
    agentID                        CHAR(10)                       null,
    ccText                         ntext                          null,
    indexImageCaption              NVARCHAR(100)                  null)  ;

--------------------------------------------------
-- Create Primary Key PK_Article
--------------------------------------------------
alter table dbo.Article 
        add constraint PK_Article 
        primary key (path);

Sample Data (current)

id,IW_State,path,contentArea,homepage,title,summary,keywords,author,type,subArea,publishDate,expireDate,articleLanguage,
indexImage,eventStartDate,eventEndDate,eventLocation,agentID,ccText,indexImageCaption
244,"Original","templatedata\Article\ColumbiaArticle\data\2002-01_wuerl_en","COL",NULL,"Reflection and Guidance",
"The first in a new series of columns on Pope John Paul II's encyclicals and other major teaching documents.",
"encyclicals, wuerl","Bishop Donald Wuerl","COL","AUTH01","2002-01-01","9999-01-01","en",NULL,NULL,NULL,NULL,NULL,NULL,NULL
241,"Original","templatedata\Article\ColumbiaArticle\data\2002-01_wuerl_es","COL",NULL,"Reflección y Guía",
"La primera de una serie de columnas acerca de las encíclas y de otros documentos didácticos del Papa Juan Pablo II.",
"wuerl, papa, juan pablo","Obispo Donald Wuerl","COL","AUTH01","2002-01-01","9999-01-01","es",NULL,NULL,NULL,NULL,NULL,NULL,NULL
245,"Original","templatedata\Article\ColumbiaArticle\data\2002-02_wuerl_en","COL",NULL,"Focus on the Future",
"In his first encyclical, Pope John Paul II studied the human condition and set the stage for the sweeping scope of 
his pontificate.","wuerl, pontificate","Bishop Donald Wuerl","COL","AUTH01","2002-02-01",
"9999-01-01","en",NULL,NULL,NULL,NULL,NULL,NULL,NULL

Sample Data (Expected results)

id,IW_State,path,contentArea,homepage,title,summary,keywords,author,type,subArea,publishDate,expireDate,articleLanguage,
indexImage,eventStartDate,eventEndDate,eventLocation,agentID,ccText,indexImageCaption
244,"Original","templatedata\Article\ColumbiaArticle\data\en\2002-01_wuerl","COL",NULL,"Reflection and Guidance",
"The first in a new series of columns on Pope John Paul II's encyclicals and other major teaching documents.",
"encyclicals, wuerl","Bishop Donald Wuerl","COL","AUTH01","2002-01-01","9999-01-01","en",NULL,NULL,NULL,NULL,NULL,NULL,NULL
241,"Original","templatedata\Article\ColumbiaArticle\data\es\2002-01_wuerl","COL",NULL,"Reflección y Guía",
"La primera de una serie de columnas acerca de las encíclas y de otros documentos didácticos del Papa Juan Pablo II.",
"wuerl, papa, juan pablo","Obispo Donald Wuerl","COL","AUTH01","2002-01-01","9999-01-01","es",NULL,NULL,NULL,NULL,NULL,NULL,NULL
245,"Original","templatedata\Article\ColumbiaArticle\data\en\2002-02_wuerl","COL",NULL,"Focus on the Future",
"In his first encyclical, Pope John Paul II studied the human condition and set the stage for the 
sweeping scope of his pontificate.","wuerl, pontificate","Bishop Donald Wuerl","COL",
"AUTH01","2002-02-01","9999-01-01","en",NULL,NULL,NULL,NULL,NULL,NULL,NULL

This one of a couple of tables that I am looking at for the conversion project... about 4000 rows in this table..

Any Help or advice would be greatly appreciated!

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 09:16:46

If you want to trim the last 3 characters off of the path column, then use this expression:

LEFT(Path, Len(Path) - 3)

However, if the number of characters after the last _ is dynamic, then you'll need something more complex.

The bit that concerns me is the primary key. I don't like storing data in primary keys. I certainly don't like using a VARCHAR(255) column as a primary key.

In the DDL you posted, the ID column would be a candidate for a primary key, not the Path column. If you want to ensure that each Path is unique, then you can create a unique constraint on the Path column.

There are 10 types of people in the world, those that understand binary, and those that don't.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-08 : 13:35:58

Edited the post to add carriage returns. Please do not post so wide with code tags next time. Add carriage returns to avoid horizontal bars in a browser.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources