Solved

how to force a default value for a field during DTS import

Posted on 2004-03-21
4
303 Views
Last Modified: 2013-11-30
Hello,
Using DTS, I am creating a table(B) that I am importing in as another(A).  in table B, I do not have a field that is necessary for table A.  SO, I have created:

CREATE TABLE [XP].[dbo].[pin0030] (
[ACCOUNTNUM] int NOT NULL,
[ACCOUNTSTAT] varchar (1) NULL,
[LASTNAME] varchar (16) NULL,
[FIRSTNAME] varchar (16) NULL,
[MIDDLENAME] varchar (3) NULL,
[ENROLLED] varchar (4) NULL,
[GR] smallint NULL,
[SECTION] varchar (4) NULL,
[HM] varchar (6) NULL,
[KEYPAD] varchar (6) NULL,
[key_field] varchar (4) NULL,
[mergedate] timestamp NULL,
[Remote_Eml] smallint (2) NULL,
[Remote_Sta] varchar (4) NULL
)
where ENROLLED = '0030' and ACCOUNTSTAT = 'A'

the issue is that I need to somehow place a default value into the [key_field] which would = 'key'

I know that this is simple, but everything I try gives me an error in the DTS.  Thanks all for your help.
0
Comment
Question by:scg911
  • 2
4 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10644653
Couple ways....

Instead of using a source table, base the input on a query and "select" the value you want for the default and then you have the column to populate your destination column.

You could also use an activex script to populate the default (slower).  Create a new transformation, choose activex as the transformation type and it would look something like this dtsdestination("key_field")="whatever".

You could also change the definition on the destination to have a default.  This would populate the field on the load and then you could remove the default after wards.


Brett
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 10644665
ALTER TABLE [XP].[dbo].[pin0030] ADD
      CONSTRAINT [DF_pin0030_key_field] DEFAULT ('key') FOR [key_field]

Also, please maintain these very old open questions:
1 12/16/2003 500 Server 2003 "stuck" at applying computer...  Open Operating Systems
2 09/30/2003 500 duplicate record, but not necessarily du...  Open Microsoft SQL Server
0
 

Author Comment

by:scg911
ID: 10644700
acperkins,

Thanks for your help, but I am trying to utilize the above create, and just be able to add some more code to the line below to make 'key' be the default value for [key_field]

[key_field] varchar (4) NULL,

also, the very old open questions, should have been closed out and under my login they all show PAQ
again, thanks for your help

scg911
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10644761
>>just be able to add some more code to the line below to make 'key' be the default value for [key_field]<<
Since you have awarded me the points is this question now resolved?

>>under my login they all show PAQ<<
Thanks you for closing that one question, But I believe you may have oerlooked this one:
12/16/2003 500 Server 2003 "stuck" at applying computer...  Open Operating Systems
http://www.experts-exchange.com/Operating_Systems/Q_20827414.html
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now