Solved

Insert data into a table from a text file from Mainframe

Posted on 2002-03-21
7
382 Views
Last Modified: 2012-05-04
Running MSSQL 6.5.

My goal is to import data into a tracking table on the sql server from a file create from a mainframe. The file will contain accountnum, accountsuffix, name, entry-date. My problem is that I do not want to duplicate information yet multiple records could contain the same accountnum but a different accountsuffix. Another problem that I am having is that when I use bcp to import the file, the leading and trailing spaces get imported, which I do not want.

What is the best solution for this problem?
0
Comment
Question by:vmorales
[X]
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
  • 4
  • 3
7 Comments
 
LVL 2

Expert Comment

by:JamesT
ID: 6886310
To take care of the duplicates issue put an unique index on accountnum and accountsuffix with ignore duplicates. As to the spaces the easiest way is to use DTS and transform the columns using rtrim() and ltrim(). If you don't want to do that then import the data, the update the table setting each column like so:

update acc_table
set accountnum = ltrim(accountnum)
...
...


That's basic but it can work.
0
 

Author Comment

by:vmorales
ID: 6886466
So the bcp utility when it copies the data into the table will ignore duplicates if the accountnum and accountsuffix are unique indexes. Is this right?

How do I create these indexes?
0
 

Author Comment

by:vmorales
ID: 6886511
I have created the indexes on accountnum and accountsuffix. However, when i import my data, the bcp aborts because of duplicate keys. For example:

Already in the table:
acctnum    acctsuff
9292        20
1111        21




In the text file:
acctnum   acctsuff
1234        20
1255        20
3242        21

This should be allowed to insert. However if both the acctnum and acctsuff from the file matched the same acctnum and acctsuff in the table, this should not be allowed.

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 2

Expert Comment

by:JamesT
ID: 6886555
Are you making the index a compound index? That is, is the index made up of acctnum and acctsuff both? Look at the syntak of the following:

CREATE TABLE ACCTINFO (
ACCTNUM VARCHAR(10),
ACCTSUFF VARCHAR(10))
GO

CREATE  UNIQUE  INDEX [IDX_UNIQUE] ON ACCTINFO(ACCTNUM, ACCTSUFF) WITH  IGNORE_DUP_KEY  ON [PRIMARY]
GO

The these statements are executed:

INSERT INTO M4863.ACCTINFO (ACCTNUM,ACCTSUFF) VALUES ('1234','20')
INSERT INTO M4863.ACCTINFO (ACCTNUM,ACCTSUFF) VALUES ('1255','20')
INSERT INTO M4863.ACCTINFO (ACCTNUM,ACCTSUFF) VALUES ('3242','21')
INSERT INTO M4863.ACCTINFO (ACCTNUM,ACCTSUFF) VALUES ('1255','20')

Get this message:


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)

Server: Msg 3604, Level 16, State 1, Line 4
Duplicate key was ignored.

0
 

Author Comment

by:vmorales
ID: 6886581
Yes.

I understand everything but the ON [PRIMARY]

0
 
LVL 2

Accepted Solution

by:
JamesT earned 200 total points
ID: 6886596
The On Primary piece tells the database where to build the index. You can leave it off and have it go to the default file or specify one if you have multiple.


CREATE  UNIQUE  INDEX [IDX_UNIQUE] ON ACCTINFO(ACCTNUM, ACCTSUFF) WITH  IGNORE_DUP_KEY  

That works fine too.
0
 

Author Comment

by:vmorales
ID: 6886611
I did a sp_helpdb [dbname] to find the segment. This works great. Just what I was asking for. Thanks for the help.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 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