November 7, 2007 at 8:14 am
hi All,
I currently have a table made up of the following and it has data in it.
column1 integer pk
column2 integer pk
column3 decimal(18,2)
I now want to introduce a datetime column as part of the primary key giving it an initial value of Getdate() I also want it placed with the other primary keys as opposed to the end of the table.
I know I can create this within the Studio, but It needs to be scripted.
Thanks.
November 7, 2007 at 9:15 am
To the best of my knowledge you can't move columns around. You can only add them to the end of the table. So what you are going to have to do is create a temp table with the format you want then copy your data over.
Probably the easiest way to generate the script is to go ahead and do it in Management Studio and instead of saving/executing generate a change script. If you right click on the table name in the object explorer and hit modify you can make the changes there. Then you can right click in the whitespace next to the columns and at the bottom will be a "generate change script" option. Select that and it will open a new connection with the generated change script.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 7, 2007 at 10:18 am
You could create a new table with the desired layout, when insert all records from the original table, drop the old table and rename the new one to the original name. But remember to recreate any foreign key constraints.
Or just accept that the new column will be listed at the end. It won't make a difference to the actual storage, because the new column is part of the primary key.
[font="Verdana"]Markus Bohse[/font]
November 8, 2007 at 2:13 am
Hi, thanks for the reply.
I've started to look at this solution and it nearly fulfills my need. The one problem that I have is that because my new column is part of the primary key, it cannot be Null since I already have data in the existing table. I therefore want to set the default to 'getdate', but it will not let me.
How can I get the column populated with getdate?
November 8, 2007 at 4:59 am
I did the following test and it worked just fine
create table test (id int not null identity (1,1), val1 char(1))
insert into test values ('A')
insert into test values ('B')
insert into test values ('C')
insert into test values ('D')
insert into test values ('E')
alter table test add lastupdate datetime not null constraint df_test_lastupdate default getdate()
select * from test
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 9, 2007 at 5:33 pm
You can always create a diagram with just the table you want to change. Then when the table is just the way you want it, right click on the white space (not on the table) and select "Generate Change Script..." You can write this script to a file or cut/paste into a script window. Do not save the diagram or it will generate and then execute the script.
If it needs to recreate the table, the script will take care of any FK relationships, constraints, whatever is defined on the table in order to create a new table, copy the data to the new table, drop the existing and rename the new table to the old name.
Every now and then the changes you make may confuse the script generator but that happens rarely.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply