copy column data to another table w.diff structure?

• ben potts

  SSC-Addicted

  Points: 414

  More actions

  April 21, 2006 at 2:12 am

  #117045

  hi, i have have a table with 2 columns.

  TheDate..........DayName

  07/01/2006.....Saturday

  08/01/2006.....Sunday

  09/01/2006.....Monday

  there are 721 rows in there and i cant remember what script i used to create the rows.

  i now need this table but with a primary key in it for another project, but i cant add a primary key to it:

  'BEN_WEEKENDSANDHOLIDAYS' table

  - Unable to modify table.

  ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'ID' cannot be added to table 'BEN_WEEKENDSANDHOLIDAYS' because it does not allow nulls and does not specify a DEFAULT definition.

  I have created another table with the same columns and a primary key column DateID.

  is there someway i can copy the data into the new table with identity on?

  or maybe someone has the script to allow me recreate the data?

  any help would be great!

  ben

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  April 21, 2006 at 2:49 am

  #633430

  Why do you want an identity column added?

  'TheDate' is your natural key it seems. Why not use that as PK?

  PS. The error you're getting is exactly for the reasons the text says.  You have something mixed up in your syntax.

  ALTER TABLE myTAB ADD newIDcolumn INT NOT NULL IDENTITY(1,1) PRIMARY KEY

  ..works

  /Kenneth

• ben potts

  SSC-Addicted

  Points: 414

  More actions

  April 21, 2006 at 2:55 am

  #633432

  ALTER TABLE myTAB ADD newIDcolumn INT NOT NULL IDENTITY(1,1) PRIMARY KEY

  worked a treat thanks!