Insert with Select statement (But change a value)

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 with Select statement (But change a value)

Author

Topic

JacobPressures
Posting Yak Master

112 Posts

Posted - 2009-04-03 : 11:33:04

Supposed i have a list of IDs, names and sections in a table.

NameID Name Section
1 Sue 24
2 Robert 24
3 Bruce 24
4 Mike 25
5 Sam 25
6 Pam 25

Now I want to take all the names where section is 25 and re-add them to the same table with a different section number. The result will be like this:

NameID Name Section
1 Sue 24
2 Robert 24
3 Bruce 24
4 Mike 25
5 Sam 25
6 Pam 25
7 Mike 30
8 Sam 30
9 Pam 30

I know I can try something like the following:

INSERT INTO tblName (Name, Section)
Select *
From tblName
Where Section = 25

But what i really want to do is change the Section to 30 not re-enter the same section number.

Thanks very much!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 11:34:35

instead of select "*" use an explicit column list and replace the [section] with a constant or expression.

Be One with the Optimizer
TG

JacobPressures
Posting Yak Master

112 Posts

Posted - 2009-04-03 : 11:48:23

Thanks for the very quick reply!!! I understood the first part. I realize the error there.

But the second part I'm not sure of. This is the best I can deduce. I actually think this will work. I'll give it a try.

INSERT INTO tblName (Name, Section)
Select Name, 30
From tblName
Where Section = 25

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-03 : 11:49:48

that query looks good.

JacobPressures
Posting Yak Master

112 Posts

Posted - 2009-04-03 : 16:27:51

Thanks TG!!!! Worked perfectly!

I'm Just Mesmerized at how fast you responded. Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 16:30:59

The speed of my responses id proportional to the extent of my boredom here at work on a Friday afternoon.