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.
| Author |
Topic |
|
NickC
Yak Posting Veteran
68 Posts |
 Posted - 2011-06-14 : 07:23:02
|
| Hello Guys,Someone asked me to insert two new columns into a table (startdate, enddate) then update the two new columns (all with the same start and end date), and then update one of them columns (as one of them as moved now so the end date needs to be change)for a single instance, then insert into the table a new row (the new startdate and endd ate for that person). here is the attached sql, i need to get it run as a single script to so IT guys dont have to play around with it :/ALTER TABLE [MPDb].[dbo].[TABLE] add StartDate datetime add EndDate datetime UPDATE [MPDb].[dbo].[TABLE]SET StartDate = '2007-07-01', EndDate = '9997-12-31'WHERE UWR is not nullUPDATE [MPDb].[dbo].[TABLE]SET EndDate = '2010-12-31'WHERE UWR = 'UWRNAME'INSERT INTO [MPDb].[dbo].[TABLE]VALUES (15, 'UWRTEAM' , 'UWRNAME', '2011-01-01', '9997-12-31')Many ThanksNick |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-14 : 07:28:14
|
ALTER TABLE [MPDb].[dbo].[TABLE]add StartDate datetime,add EndDate datetime |
 |
|
|
NickC
Yak Posting Veteran
68 Posts |
Posted - 2011-06-14 : 07:31:47
|
| Thanks that helps, once I put comma in too ( sorry I'm not gr8 at SQL), now though I'm getting" Msg 207, Level 16, State 1, Line 6Invalid column name 'StartDate'.Msg 207, Level 16, State 1, Line 6Invalid column name 'EndDate'."Which I assume because its all one transaction, where as if it wasnt then it would work?! is there a way round this?! |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-14 : 07:42:55
|
| You either need a go after the ddl or to run the dml as dynamic sql. They can't be in the same batch otherwise the dml won't compile.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
NickC
Yak Posting Veteran
68 Posts |
Posted - 2011-06-14 : 08:50:46
|
| You had me until ddl lol - its quite sad i'm a junior reports developer and lost - normally its easy due to a good datawarehouse - all these updates / insert etc confuse me .... may need a course lol - I'll google the terms and work it out. thanks guys! :) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-14 : 09:55:18
|
| DDL - Data definition language (CREATE, ALTER, DROP)Put a GO between the ALTER and the UPDATE statements so that SQL runs them as two separate batches.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Altering a table then update in single script