Solved

Insert data into a table from a text file from Mainframe

Posted on 2002-03-21
7
379 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

856 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