Data Type conversion

 

i'm working with client and i'm migrating them (poorly designed) access database on sql express 2005 database. user id data in varchar column. i've decided move int- identity column make user id convention easier , less user-intensive (don't ask). here's issue:

 

the current data in solicitation list table linked donation table, joined via user id. new table is a patron info table linked donation table via user id. i've imported old tables access database , left relationships intact. able move people in solicitation list on new patron table, , they've been assigned int-identity user ids. how move old donation data on new table, (while keeping relationships the patrons intact) without having input 9,000+ records in manually?

 

is there way convert varchar user id column int-identity, autonumbers, , updates corresponding data in donations table accordingly?

hi,

i'd go in 2 stepa change each table..

you import data "as is" access database.. means leaving old varchar id column(s) remains is... imported table serves work table, not required define , enforce constraints, both domains , integrity ones, if you should of course "check import result" correct

 

then can define , create new "real" table required, correct data types, constraints (not fk) , like.. copy data work table destination table allowing override of identity automatic generated value via proper statement, set identity_insert ... off;

obviously data pump consider cast of required columns, can

insert destinationtable selecting convert(new_datatype, old_value) worktable...

when done, involved tables, can delete work tables , define required fk constraints...

similar (only 1 table no fks)

set nocount on;  use tempdb;  go  create table dbo.t (    id varchar(10) not null,   data varchar(20) not null   );  go  insert dbo.t values ( '1', 'd1' );  insert dbo.t values ( '2', 'd2' );  insert dbo.t values ( '4', 'd4' );  insert dbo.t values ( '6', 'd6' );  insert dbo.t values ( '15', 'd..' );  insert dbo.t values ( '10', 'd..' );  insert dbo.t values ( '20', 'd..' );  go  print 'converted original data..';  select * dbo.t;  go  print 'creating actual real table convenient..';  create table dbo.newt (   id int not null identity primary key,   data varchar(10)   );  go  print 'allowing insertion data maintaining original id.toint values..';  set identity_insert dbo.newt on;  go  print 'inserting data casting original (string) id int..';  insert dbo.newt (id, data)   select convert(int, id), data    dbo.t    order convert(int, id)  go  print 'disabling identity override..';  set identity_insert dbo.newt off;  go  print 'selecting new actual real table correct datatypes..';  print 'after insert operation old work table can deleted..';  select * dbo.newt order id;  go  print 'perform same operation involved tables..';  print '';  print 'after imports have been performed, can drop all';  print 'work tables , arrange referential integrity constraints..';  go  drop table dbo.t, dbo.newt;  --<---------  converted original data..  id         data  ---------- --------------------  1          d1  2          d2  4          d4  6          d6  15         d..  10         d..  20         d..  creating actual real table convenient..  allowing insertion data maintaining original id.toint values..  inserting data casting original (string) id int..  disabling identity override..  selecting new actual real table correct datatypes..  after insert operation old work table can deleted..  id          data  ----------- ----------  1           d1  2           d2  4           d4  6           d6  10          d..  15          d..  20          d..  perform same operation involved tables..     after imports have been performed, can drop  work tables , arrange referential integrity constraints..  

regards

SQL Server  >  SQL Server Express