September 9, 2008 at 4:08 am
I have two sql server CE databases, in each database there is table with Identity column. I want to migrate the data from one database to another ( the data for the table which has the identity column), but i am getting error and unable to migrate the data.
Can anyone tell me how to do this, as in SQL Server CE we cannot set the Identity_insert on/off on the table..please help to solve this issue
September 9, 2008 at 4:14 am
Is this done using a script?
If so then you could name all the columns you inserting into that way it won't try and insert into the identity column.
e.g
INSERT INTO table1
(col1,col2)
SELECT col1,col2
FROM table2
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 9, 2008 at 4:28 am
yes, it is done through scripts.. but the problem is it is coming from a table with identity column and getting inserted to a table with identity column.. this is where i am unable to add the data ..
for eg. table1 (col1 (indentity), col2) in database1
table2 (col1 (identity), col2) in database2
in table1 of database1 there are 10 rows and i want to insert these 10 rows to table2 of database2..
Note: Both the databases are SQL SERVER CE 2.0
September 9, 2008 at 4:39 am
Are wanting to insert the values of Col1 & col2 or just col2 and then get new values for col1 upon insert?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 9, 2008 at 4:46 am
insert both the values in Col1 and col2 from table1 to col1 and col2 in table2
September 9, 2008 at 4:51 am
with out identity_Insert, I'm not 100% sure how to do it, sorry, I'm hoping someone else can help you 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 9, 2008 at 5:03 am
Thanks Chris..
Please can any body help
September 10, 2008 at 5:19 am
you need to modify your script for eg.
if your table1(inwhich you want to insert the data) has col1(identity col),col2,col3 and table2 has col1(identity col),col2,col3 then the script should be
insert into table1(col2,col3) select col2,col3 from table2.
col1 value will be automatically generated for table1 upon new inserts.
Hope it helps!
February 10, 2009 at 11:54 pm
alter table [dbo.TBLCATEGORY]
alter column ID IDENTITY(4981,1)
;
Insert into [dbo.TBLCATEGORY] (PARENT,TITLE,TYPE) values (1204,'tt',0);
(dbo.TBLCATEGORY ID is identity column)
February 12, 2009 at 9:24 am
How about moving the data into a temporary table without an identity column. Adding records to the temporary table, such that there are no gaps in the identities, and the placeholder records are readily identifiable.
At this point, it will be safe to insert the records into the destination, relying on the order of insert to keep the identity numbers in tact. You might even want to add a column to the destination table to hold the original identity as an error check.
Now, remove the placeholders and the original identity column.
To fill the gaps, you will need to make a temporary table with as many numbers as your identity upper limit.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply