Solved

Insert data into a table from a text file from Mainframe

Posted on 2002-03-21
7
384 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

719 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