Solved

Insert data into a table from a text file from Mainframe

Posted on 2002-03-21
7
377 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
  • 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 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

21 Experts available now in Live!

Get 1:1 Help Now