Solved

Insert data into a table from a text file from Mainframe

Posted on 2002-03-21
7
375 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 2

Expert Comment

by:JamesT
Comment Utility
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
Comment Utility
Yes.

I understand everything but the ON [PRIMARY]

0
 
LVL 2

Accepted Solution

by:
JamesT earned 200 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

771 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

10 Experts available now in Live!

Get 1:1 Help Now