dbaker is correct and I have personally had to go through this with a db upgrade from SQL 2000 to SQL 2005. I went ahead and did the upgrade and a month later it was realized that the collation of the database was different from the requested collation of the new install (rookie move by me)...

So it was decided I had to script everything out, and re-create all the objects for a new database with the correct collation and then use SSIS to do a simple import to all the tables...ect...

Issue was that the import wizard kept on erroring out due to some sort of error with IDENTITY columns...

I had to create a script that built ALL the insert statements for each table.

The script analyzed each table to determine if it had an IDENTITY column and would handle it appropriately...

Here is the script and you can use it if you like...

/*

-----------------------------------------------------------------------------------------

This script was created when I experienced an issue with SQL 2005 and the

IMPORT/EXPORT wizard which continually failed requiring manual intervention.

-----------------------------------------------------------------------------------------

Pre-STEPS

----------

I had to re-create the database, logins, schemas...by scripting it out. The database

was originally restored with the wrong collation

SO I had to attempt to re-create the database by scripting it out with all the object

level permissions, logins...ect.

THEN IMPORT all the data to the new database tables...problem was the IMPORT wizard

continued to fail with IDENTITY field errors even though the wizard was set to allow this...

So this script gets around that...

The script disables all triggers, then builds an INSERT INTO script

based on the SOURCE and DESTINATION databases.

In my example I renamed the current database something like DATABASE_OLD

then created the new database with the correct name

Ran the script to create the INSERT Statements

Ran the script which loaded all the tables

Verified the table counts

-----------------------------------------------------------------------------------------

*/

------------------------------------------

-- SQL 2005 Build Insert statement script

------------------------------------------

SET NOCOUNT ON

DECLARE @SQLCmd VARCHAR(MAX),

@schemaid INT,

@objectid INT,

@is_identityINT,

@loopcnt INT,

@DestinationDB SYSNAME,

@SourceDB SYSNAME

---------------------------------------------------------

-- you need to enter the source and destination databases

-- and make sure you are in the source database when the

-- script is run.

---------------------------------------------------------

SET @DestinationDB = ''

SET @SourceDB = ''

SET @loopcnt = 0

---------------------------------------------------------------------------------------

-- create script to DISABLE all triggers for all tables within the destination database

---------------------------------------------------------------------------------------

PRINT '-- Disable All Triggers'

PRINT '-----------------------'

SELECT'USE ' + @DestinationDB + CHAR(13) + CHAR(10) + 'GO'

SELECT 'ALTER TABLE ' + @DestinationDB + '.dbo.' + tables.name + ' DISABLE TRIGGER ALL ' + CHAR(13) + CHAR(10)

FROMsys.schemasschemas(NOLOCK)

INNER JOIN sys.tables tables(NOLOCK) on (schemas.schema_id = tables.schema_id)

WHEREtables.type = 'U'

-------------------------------------------------------

-- get a list of all the tables, schema_id, & object_id

-------------------------------------------------------

SELECT

tables.schema_id,

tables.name,

tables.object_id,

columns.is_identity

INTO

#InsertBuild

FROM

sys.tables tables(NOLOCK)

INNER JOIN sys.columns columns(NOLOCK) on tables.object_id = columns.object_id

WHERE

schema_name(tables.schema_id) in ('dbo')AND

tables.type = 'U'

ORDER BY

columns.is_identity DESC,

tables.schema_id,

tables.name,

object_name(tables.object_id)

---------------------------------------------------------------------------------------------

-- select inital table to be scripted out and assign the values to the schema_id & object_id.

-- if the table has an identity field it will use the first sqlCmd which includes the

-- IDENTITY_INSERT ON clause otherwise it will use a general INSERT statement.

---------------------------------------------------------------------------------------------

SELECT TOP 1

@schemaid = schema_id,

@objectid = object_id,

@is_identity = is_identity

FROM #InsertBuild

WHILE (SELECT count(*) FROM #InsertBuild) > 0

BEGIN

------------------------------------------------

-- figure out if the table has an identity field

------------------------------------------------

IF @is_identity = 1

BEGIN

SELECT @SQLCmd ='SET IDENTITY_INSERT ' + @DestinationDB + '.dbo.' + tables.name + ' ON ' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +

'INSERT INTO ' + @DestinationDB + '.dbo.' + tables.name + CHAR(10) + '(' + CHAR(13) + CHAR(10)

FROM

sys.schemasschemas

INNER JOIN sys.tables tables on (schemas.schema_id = tables.schema_id)

WHERE

schemas.schema_id = @schemaid and

tables.object_id = @objectid

END

ELSE

BEGIN

SELECT@SQLCmd = 'INSERT INTO ' + @DestinationDB + '.dbo.' + tables.name + CHAR(10) + '(' + CHAR(13) + CHAR(10)

FROM

sys.schemasschemas

INNER JOIN sys.tables tables on (schemas.schema_id = tables.schema_id)

WHERE

schemas.schema_id = @schemaid and

tables.object_id = @objectid

END

---------------------------------------------------------------------------------------------------------------------------------------

-- builds a list of all column names for the current table and append the values to the end of the column names to the INSERT statement

---------------------------------------------------------------------------------------------------------------------------------------

SELECT@SQLCmd= @SQLCmd + '[' + columns.name + '],' + CHAR(13) + CHAR(10)

FROM

sys.schemasschemas (NOLOCK)

INNER JOIN sys.tables tables (NOLOCK) on (schemas.schema_id = tables.schema_id)

INNER JOIN sys.columns columns (NOLOCK) on (tables.object_id = columns.object_id)

WHEREschemas.schema_id = @schemaid and

tables.object_id = @objectid

ORDER BY

tables.name,

columns.column_id

--------------------------------------------------------------

-- clips off the last comma at the end of the select statement

--------------------------------------------------------------

SET @SQLCmd = substring(@SQLCmd, 1, len(@SQLCmd) - 3) + CHAR(13) + ')' + CHAR(13) + CHAR(10) +

'SELECT' + CHAR(13) + CHAR(10)

---------------------------------------------------------------------------------------------------------------------------------------

-- builds a list of all column names for the current table and append the values to the end of the column names to the INSERT statement

---------------------------------------------------------------------------------------------------------------------------------------

SELECT@SQLCmd= @SQLCmd + '[' + columns.name + '],' + CHAR(13) + CHAR(10)

FROMsys.schemasschemas(NOLOCK)

INNER JOIN sys.tables tables(NOLOCK) on (schemas.schema_id = tables.schema_id)

INNER JOIN sys.columnscolumns(NOLOCK) on (tables.object_id = columns.object_id)

WHERE

schemas.schema_id = @schemaidand

tables.object_id = @objectid

ORDER BY

tables.name,

columns.column_id

--------------------------------------------------------------

-- clips off the last comma at the end of the select statement

--------------------------------------------------------------

SET @SQLCmd = substring(@SQLCmd, 1, len(@SQLCmd) - 3) + CHAR(13) + CHAR(10)

----------------------------------------------------------------------------------------------

-- identify the table name and turn off identity_insert option if table has an identity field

----------------------------------------------------------------------------------------------

IF @is_identity = 1

BEGIN

SELECT @SQLCmd = @SQLCmd + 'FROM ' + @SourceDB + '.dbo.' + tables.name +CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +

'SET IDENTITY_INSERT ' + @DestinationDB + '.dbo.' + tables.name + ' OFF ' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +

'--------------------------------------------------------------'

FROMsys.schemasschemas(NOLOCK)

INNER JOIN sys.tables tables (NOLOCK) on (schemas.schema_id = tables.schema_id)

WHERE

schemas.schema_id = @schemaidand

tables.object_id = @objectid

END

ELSE

BEGIN

SELECT @SQLCmd = @SQLCmd + 'FROM ' + @SourceDB + '.dbo.' + tables.name + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +

'--------------------------------------------------------------'

FROMsys.schemasschemas(NOLOCK)

INNER JOIN sys.tables tables (NOLOCK) on (schemas.schema_id = tables.schema_id)

WHERE

schemas.schema_id = @schemaidand

tables.object_id = @objectid

END

------------------------------------------------------------

-- print the statements that will be used to import the data

------------------------------------------------------------

PRINT @SQLCmd

-- EXEC (@SQLCmd)

-- remove the current table from the temp table

-----------------------------------------------

DELETE FROM #InsertBuild

WHERE schema_id = @schemaid and

object_id = @objectid

-- select the next table to be built

------------------------------------

SELECT TOP 1

@schemaid = schema_id,

@objectid = object_id,

@is_identity= is_identity

FROM #InsertBuild

END

PRINT '-- Enable All Triggers'

PRINT '----------------------'

-- create script to enable all triggers for all tables within the destination database

---------------------------------------------------------------------------------------

SELECT 'ALTER TABLE ' + @DestinationDB + '.dbo.' + tables.name + ' ENABLE TRIGGER ALL '

FROM

sys.schemasschemas

INNER JOIN sys.tables tables on (schemas.schema_id = tables.schema_id)

WHEREtables.type = 'U'

-- finished with the script, drop the temp table

------------------------------------------------

DROP TABLE #InsertBuild

SET NOCOUNT OFF