insert and replace one column from excel

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)

insert and replace one column from excel

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2010-04-22 : 10:03:27

ms sql server 2005 standard edition

---
I have given the task of doing a straight insert and replace into a table replacing some of the data in one column.

looks like what they did was a select * from the table, exported it to excel and then added a column called newpath to the spreadsheet. they want the path column updated with the data they have populated the newpath excel column with.

path column is the primary key in this table with two child tables. I understand that I will have to drop the primary key in the article table then drop the FKs in the child tables - make the changes to the child tables first, then the article table then add back the constraints. table DDL for the primary (parent) table below:

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)

not really sure how to code the SQL for this "insert a replace" can anyone help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 10:13:01

use OPENROWSET. have a look at syntax in books online.

it will be like


UPDATE t
SET t.Path=xl.newpath
FROM table t
INNER JOIN OPENROWSET(...)xl
ON...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-23 : 03:57:51

Refer this for OPENROWSET with EXCEL
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2010-04-23 : 07:42:15

one of my coworkers came up with a plan that worked like a charm.

we took their input ( update ) file which was basically two column excel. if the path column is this value - make it this value. converted that to .csv and BCP'd it into a temp table called article_path_convert.

then dropped the PKs and FKs on parent and all child tables then ran this update against each table :

update article
set article.path = article_path_convert.newpath
from article inner join article_path_convert 
on (article.path = article_path_convert.path)
go

then put all PF and FK constraints back.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources