Sql-server – Import data problem in SQL Server 2008

importsql serversql-server-2008

I want to import data from one database to another. On the destination database, I don't have permission to restore a backup file to it, so I have only one option which is to import data.

I used the wizard and I set the option of allow identity insert, but after it ran I faced a problem with foreign keys insertion. Is there anything I missed or shall I drop the foreign keys and recreate it after import?

I really wonder why Microsoft made it complicated.

Best Answer

You could drop the foreign keys, but if the tables are large and/or you have busy environment, be sure to test your insert times before you do anything on production. In regard to new keys, it shouldn't throw a problem, but if you're trying to insert duplicate keys then you'll get errors. Also, be sure to insert parent entities first (if you don't drop foreign constraints first!) then child entities.

Related Solutions

Sql-server – Moving tables to another SQL2008 database (including indexes, triggers, etc.)

Scripting out the tables, then using SSIS to transfer the data would be the most reliable and effective way of moving the data to the new database.

Eliminate database fragmentation (export, drop and re-import tables)

Instead of export/import, you should use

alter table t shrink space;

which is much less dangerous & keeps the table even accessable during the reorganization. After the shrink, you may use below script (originally from Tom Kyte) to find out to what size you can resize (making them smaller) the datafile(s):

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

Best Answer

Related Solutions

Sql-server – Moving tables to another SQL2008 database (including indexes, triggers, etc.)

Eliminate database fragmentation (export, drop and re-import tables)

Related Question