Migrating data in sql server from source to target with Schema change in target

Question

Migrating data in sql server from source to target with Schema change in target

Viewing 4 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

If you have default constraint on the column and do not specify this column in the column list of insert statements it should work.

Could you please post full ddl of your destination table including default constraint if any and small cut from your auto-generated insert script.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

PiratePrasad SSC Rookie Points: 29 More actions · Answer 2

USE [prgtest]

GO

/****** Object: Table [dbo].[Department] Script Date: 04/17/2012 15:49:39 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Department](

[DeptNo] [int] NOT NULL,

[DeptName] [nvarchar](max) NOT NULL,

[EmpCount] [int] NOT NULL,

[Location] [nvarchar](max) NOT NULL,

CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED

(

[DeptNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Department] ADD DEFAULT ('USA') FOR [Location]

GO

The scripts generated is given below

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON

GO

SET DATEFORMAT YMD

GO

SET XACT_ABORT ON

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

GO

BEGIN TRANSACTION

-- Pointer used for text / image updates. This might not be needed, but is declared here just in case

DECLARE @pv binary(16)

-- Add 5 rows to [dbo].[Department]

INSERT INTO [dbo].[Department] ([DeptNo], [DeptName], [EmpCount]) VALUES (100, N'Sales', 2)

INSERT INTO [dbo].[Department] ([DeptNo], [DeptName], [EmpCount]) VALUES (200, N'Accounts', 3)

INSERT INTO [dbo].[Department] ([DeptNo], [DeptName], [EmpCount]) VALUES (300, N'Finance', 1)

INSERT INTO [dbo].[Department] ([DeptNo], [DeptName], [EmpCount]) VALUES (400, N'IT', 2)

INSERT INTO [dbo].[Department] ([DeptNo], [DeptName], [EmpCount]) VALUES (500, N'Accessories', 3)

COMMIT TRANSACTION

GO

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Placed your code into my query window and executed it.

No problems at all! All five records inserted with Location set to USA a per default.

Are you sure you are getting the error in that exactly spot?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

PiratePrasad SSC Rookie Points: 29 More actions · Answer 4

Thanks a lot friend....i found the issue, if the column is not null without any default value, it is throwing error, in this condition it is working fine.