Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2004-03-21
Medium Priority
Last Modified: 2013-11-30
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] (
[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.
Question by:scg911
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 34

Expert Comment

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.

LVL 75

Accepted Solution

Anthony Perkins earned 1000 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

Author Comment

ID: 10644700

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

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

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

715 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