insert and replace data in one column

Question

insert and replace data in one column

Jpotucek

SSCoach

Points: 18583
More actions
April 22, 2010 at 8:01 am

#223736

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?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 1

Did you say that the 'path' column serves as the primary key on this table? Strikes me as a little odd....

I've never faced this exact situation, but it seems to me that you could turn on the ON DELETE CASCADE option on the foreign key relationship you've defined between this table and the child tables, then you could simply update the primary key here and SQL would handle updating the child tables. No need to drop relationships and update the child tables separately.

Rob Schripsema
Propack, Inc.

Rob Schripsema SSCertifiable Points: 7469 More actions · Answer 2

Oops, not ON DELETE CASCADE, I meant to say, ON UPDATE CASCADE.

Sorry.

Rob Schripsema
Propack, Inc.

Jpotucek SSCoach Points: 18583 More actions · Answer 3

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!