April 17, 2012 at 3:33 am
We have a strange scenario, in which source DB's (sql server 2005) table will just have the data with 5 columns and the target(sql server 2008 R2) contains an extra column which is not null and has a default value for that column. We need to push the data for other columns from source to target. If we use any tool like red-gate, dbcompare, it will compare source with destination and then gives the query to insert the new data on to the target, but the deployment fails as it is not inserting the newly introduced column which is not null. Any idea on how to check the schema difference
For example there is a department table with 3 columns (Deptno, DeptName and EmpCount) in the source server. This table contains 5 rows. The target table has 4 columns (Deptno, Deptname, EmpCount and Location). This table doesn't have any data, but the location field is a not null with default value 'ohio'. We want to move the data from source to target using a script generated out of any tool or by writing our own script. When we execute the tool generated script it fails saying cannot insert null value to column location as the script simply inserts only the 3 columns from source.
Can somebody help me on this? how can we identify the extra column and the details abt the column in the target server.
April 17, 2012 at 4:08 am
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.
April 17, 2012 at 4:21 am
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
April 17, 2012 at 4:30 am
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?
April 17, 2012 at 5:46 am
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.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply