changing field from read to read/write

I am trying to import some data into a table for testing purposes, one of the table's fields is a read only field -
I need to change this so that I can import the data and test the stored proc for reports.
Can someone tell me how to do this hard coding or otherwise
I am using 2008Server
TagomAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris MangusDatabase AdministratorCommented:
Please script out the structure of the table and upload it.  I suspect if you are getting some information that says it's read-only that you are looking at a table with a calculated column, which you can't INSERT to.
TagomAuthor Commented:
USE [acisql2]
GO

/****** Object:  Table [dbo].[CC_CalloutLog]    Script Date: 10/04/2011 13:11:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CC_CalloutLog](
      [CLID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
      [CLIndex] [int] NOT NULL,
      [CLChannelStatus] [varchar](50) NOT NULL,
      [CLCrewName] [uniqueidentifier] NOT NULL,
      [CLEmpID] [varchar](50) NOT NULL,
      [CLInfo1] [varchar](50) NOT NULL,
      [CLInfo2] [varchar](50) NOT NULL,
      [CLInfo3] [varchar](50) NOT NULL,
      [CLInfo4] [varchar](50) NOT NULL,
      [CLStartCallTime] [datetime] NOT NULL,
      [CLNextCallTime] [datetime] NOT NULL,
      [CLEndCallTime] [datetime] NOT NULL,
      [CLAttempts] [smallint] NOT NULL,
      [CLResultofLastAttempt] [varchar](50) NOT NULL,
      [CLAttemptsString] [varchar](100) NOT NULL,
      [CLAccepted] [varchar](3) NOT NULL,
      [CLPhone1] [varchar](50) NOT NULL,
      [CLPhone2] [varchar](50) NOT NULL,
      [CLPhone3] [varchar](50) NOT NULL,
      [CLPhone4] [varchar](50) NOT NULL,
      [CLCallInfo1] [varchar](50) NOT NULL,
      [CLCallInfo2] [varchar](50) NOT NULL,
      [CLCallInfo3] [varchar](50) NOT NULL,
      [CLCallInfo4] [varchar](50) NOT NULL,
      [CLPIN] [varchar](50) NOT NULL,
      [CLCallingSequence] [uniqueidentifier] NOT NULL,
      [CLScript] [uniqueidentifier] NOT NULL,
      [CLDistrict] [varchar](50) NOT NULL,
      [CLUserID] [varchar](50) NOT NULL,
      [CLCrewCode] [tinyint] NOT NULL,
      [CLPriority] [varchar](15) NOT NULL,
      [CLUtilityID] [uniqueidentifier] NULL,
      [CLMessage] [int] NOT NULL,
      [clPagerString] [varchar](200) NOT NULL,
      [CLAtWork] [datetime] NULL,
      [CLCNCrewName] [varchar](50) NOT NULL,
      [CLScriptsName] [varchar](50) NOT NULL,
      [CLCSSequenceName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_CC_CalloutLog] PRIMARY KEY CLUSTERED
(
      [CLID] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF_CC_CalloutLog_CLID]  DEFAULT (newid()) FOR [CLID]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLInd__08AB2BC8]  DEFAULT ((0)) FOR [CLIndex]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLCha__099F5001]  DEFAULT ('') FOR [CLChannelStatus]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLEmp__0A93743A]  DEFAULT ('') FOR [CLEmpID]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLInf__0B879873]  DEFAULT ('') FOR [CLInfo1]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLInf__0C7BBCAC]  DEFAULT ('') FOR [CLInfo2]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLInf__0D6FE0E5]  DEFAULT ('') FOR [CLInfo3]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLInf__0E64051E]  DEFAULT ('') FOR [CLInfo4]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLEnd__0F582957]  DEFAULT (getdate()) FOR [CLEndCallTime]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLAtt__104C4D90]  DEFAULT ((0)) FOR [CLAttempts]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLRes__114071C9]  DEFAULT ('') FOR [CLResultofLastAttempt]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLAtt__12349602]  DEFAULT ('') FOR [CLAttemptsString]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLAcc__1328BA3B]  DEFAULT ('') FOR [CLAccepted]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLPho__141CDE74]  DEFAULT ('') FOR [CLPhone1]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLPho__151102AD]  DEFAULT ('') FOR [CLPhone2]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLPho__160526E6]  DEFAULT ('') FOR [CLPhone3]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLPho__16F94B1F]  DEFAULT ('') FOR [CLPhone4]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLCal__17ED6F58]  DEFAULT ('') FOR [CLCallInfo1]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLCal__18E19391]  DEFAULT ('') FOR [CLCallInfo2]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLCal__19D5B7CA]  DEFAULT ('') FOR [CLCallInfo3]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLCal__1AC9DC03]  DEFAULT ('') FOR [CLCallInfo4]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLPIN__1BBE003C]  DEFAULT ('') FOR [CLPIN]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLDis__1CB22475]  DEFAULT ('') FOR [CLDistrict]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLUse__1DA648AE]  DEFAULT ('') FOR [CLUserID]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLCre__1E9A6CE7]  DEFAULT ((1)) FOR [CLCrewCode]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLPri__1F8E9120]  DEFAULT ('') FOR [CLPriority]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_Callou__CLMes__29F710FA]  DEFAULT ((0)) FOR [CLMessage]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__cc_callou__clPag__2454DCEB]  DEFAULT ('') FOR [clPagerString]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_CALLOU__CLCNC__53A4C8B1]  DEFAULT ('') FOR [CLCNCrewName]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_CALLOU__CLScr__5498ECEA]  DEFAULT ('') FOR [CLScriptsName]
GO

ALTER TABLE [dbo].[CC_CalloutLog] ADD  CONSTRAINT [DF__CC_CALLOU__CLCSS__558D1123]  DEFAULT ('') FOR [CLCSSequenceName]
GO

TagomAuthor Commented:
the column that hangs first for read only is the [CLIndex] column
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

ralmadaCommented:
a) Please show us the insert query you're using and also indicate where you're using it (Is it in a trigger/stored procedure, etc...?)

b) Are you trying to insert to this table through a view?
TagomAuthor Commented:
I am using the "import wizard" in ms sql studio management
transferring data from one database to another...
basically need to combine the databases so if there is another way, i am all about it!
I need to merge the databases somehow. All table structures are the same in the database.
ralmadaCommented:
well, first of all make sure your account have adequate privileges on both databases

you can run the following in Management studio.

insert database1.dbo.yourtable
select *
from database2.dbo.yourtable

TagomAuthor Commented:
these are the errors

Validating (Error)
Messages
Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "ACIndex".
 (SQL Server Import and Export Wizard)
 
Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
 (SQL Server Import and Export Wizard)
 
Error 0xc004706b: Data Flow Task 1: "component "Destination - CC_ActiveCallouts" (121)" failed validation and returned validation status "VS_ISBROKEN".
 (SQL Server Import and Export Wizard)
 
Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
 (SQL Server Import and Export Wizard)
 
Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
 (SQL Server Import and Expor
ralmadaCommented:
if more than one table (and assuming the name is the same
use database1
exec sp_MSforeachtable 'insert [?]
			select * 
			from database2.dbo.[?]'

Open in new window

ralmadaCommented:
don't use the wizard, use management studio to run the query directly
TagomAuthor Commented:
This is the message I get now:
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'acisql2.dbo.CC_ActiveCallouts' can only be specified when a column list is used and IDENTITY_INSERT is ON.
ralmadaCommented:
ok, the problem is that you're trying to insert a value in an identity column

you might want to try

set indentity_insert acisql2.dbo.CC_ActiveCallouts  on

and then run the insert

finally make sure you reset it to off again

set indentity_insert acisql2.dbo.CC_ActiveCallouts  off
TagomAuthor Commented:
'indentity_insert' is not a recognized SET option.
ralmadaCommented:
type (extra n)

set identity_insert acisql2.dbo.CC_ActiveCallouts  on

TagomAuthor Commented:
lost me, if i just copy paste the above code i get an error...says incorrect syntax near 'extra'
Ephraim WangoyaCommented:

if you are going to use the import wizard, make sure to select the option for writing your own query.
For the query, exclude the first two fields of your table

select  [CLChannelStatus],
           [CLCrewName],
           [CLEmpID],
           [CLInfo1],
           ....
from [dbo].[CC_CalloutLog

If you are to use a query, again simple exclude the two fields

insert into [dbo].[CC_CalloutLog2(
           [CLChannelStatus],
           [CLCrewName],
           [CLEmpID],
           [CLInfo1],
           ....)
select  [CLChannelStatus],
           [CLCrewName],
           [CLEmpID],
           [CLInfo1],
           ....
from [dbo].[CC_CalloutLog

If you want the fields to be exactly the same, then set identiy insert to on as suggested above, eg

set identity_insert  [dbo].[CC_CalloutLog2  on
insert into [dbo].[CC_CalloutLog2
select * from [dbo].[CC_CalloutLog]
set identity_insert  [dbo].[CC_CalloutLog2  off


BTW: I dont understand exactly why [CLCrewName] is a unique identifier

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ralmadaCommented:
You need to enter

set identity_insert acisql2.dbo.CC_ActiveCallouts on
TagomAuthor Commented:
hand coding worked! my concern is, will this delete the rows already in the table or will it append to what is already there!
Anthony PerkinsCommented:
It depends.
ralmadaCommented:
>>will this delete the rows already in the table or will it append to what is already there! <<

My understanding is that it will NOT delete the rows already in the table. Now what could happen is that your identity column might get corrupted if you're trying to insert rows with an existing identity number in the receiving table. Remember that an identity column is meant to be unique.
ralmadaCommented:
hmmm, I'm not sure why this disposition. You've mentioned you've gone with the hand coding, and that's what I've mentioned in my comment http:#a36912761 plus I've guided you to identify the problem which was the fact that you were trying to insert a value into an identity column.

Can you please advise?
TagomAuthor Commented:
I actually thought I split the points up. Yes you were correct in your answer, however the example of how to do so is more what I needed. I did not have to turn anything on or off, and all the data inserted once I wrote the insert code correctly. I have no problem with a request to reassign points. If I new how or what to do to make this happen. I would
ralmadaCommented:
>>I have no problem with a request to reassign points. If I new how or what to do to make this happen. I would <<

click on the request attention link above and ask for a moderator to re open the questions to reassign the points.

Thanks.
TagomAuthor Commented:
done, thank you for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.