JMartin32
asked on
MS SQL QUERY/INSERT Statement
I am new to SQL and experiencing a couple of challenges in formatting SQL queries.
1.) I execute a MS SQL SELECT query from Enterprise manager and send out put to an excel spreadsheet and get unformated presentation in EXCEL, not correct format. The SQL command runs successfully but the output is not formated as desired.
2.) I execute an INSERT statement inot a employee table record called job description and recieve the following errors:
.insert into job_title
(EEO_Classification, Job_title, Job_description, Exempt_status)
values ('Office/Clerical', 'Accounting Clerk', 'Computes, classifies records, and verifies numerical data for use in maintaining accounting records.', 'NE');
Results: Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Is there some special formating required? Also, this description should include the following:
Job Title: Accounting Clerk
Computes, classifies, records, and verifies numerical data for use in maintaining
accounting records.
Essential Duties
1. Compiles and sorts documents, such as invoices and checks,
substantiating business transactions.
2. Verifies and posts details of business transactions, such as funds received
and disbursed, and totals accounts to ledgers or computer spreadsheets
and databases.
3. Audits invoices against purchase orders, researches discrepancies, and
approves for payment.
4. Computes and records charges, refunds, cost of lost or damaged goods,
freight charges, rentals and similar items.
5. Prepares vouchers, invoices, checks, account statements, reports and
other records and reviews for accuracy.
6. Reconciles general ledger accounts with various registers.
7. Reconciles bank statements.
8. Monitors accounts payable and receivable to ensure that payments are up
to date.
9. Assists employees, vendors, clients, or customers by answering questions
related to accounts, procedures, and services.
Required Qualifications and Experience
The accounting clerk must have excellent math and accounting skills, in addition
to the ability to use Quick Books software for maintaining records.
Does this amount of data require some special customization also?
Thank you.
1.) I execute a MS SQL SELECT query from Enterprise manager and send out put to an excel spreadsheet and get unformated presentation in EXCEL, not correct format. The SQL command runs successfully but the output is not formated as desired.
2.) I execute an INSERT statement inot a employee table record called job description and recieve the following errors:
.insert into job_title
(EEO_Classification, Job_title, Job_description, Exempt_status)
values ('Office/Clerical', 'Accounting Clerk', 'Computes, classifies records, and verifies numerical data for use in maintaining accounting records.', 'NE');
Results: Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Is there some special formating required? Also, this description should include the following:
Job Title: Accounting Clerk
Computes, classifies, records, and verifies numerical data for use in maintaining
accounting records.
Essential Duties
1. Compiles and sorts documents, such as invoices and checks,
substantiating business transactions.
2. Verifies and posts details of business transactions, such as funds received
and disbursed, and totals accounts to ledgers or computer spreadsheets
and databases.
3. Audits invoices against purchase orders, researches discrepancies, and
approves for payment.
4. Computes and records charges, refunds, cost of lost or damaged goods,
freight charges, rentals and similar items.
5. Prepares vouchers, invoices, checks, account statements, reports and
other records and reviews for accuracy.
6. Reconciles general ledger accounts with various registers.
7. Reconciles bank statements.
8. Monitors accounts payable and receivable to ensure that payments are up
to date.
9. Assists employees, vendors, clients, or customers by answering questions
related to accounts, procedures, and services.
Required Qualifications and Experience
The accounting clerk must have excellent math and accounting skills, in addition
to the ability to use Quick Books software for maintaining records.
Does this amount of data require some special customization also?
Thank you.
ASKER
1.) The SQL query analyzer runs successfully but the output is not presented in a format structure for EXCEL, I can send you the EXCEL document to give you an ideal, let me know if you want me to send to you. I am trying to find out if there is a way to format output from SQL into EXCEL so that I do not have to manaually modify EXCEL to meet the desired output.
2.) What field size need to change to accommodate the size (Job_description)?
Thanks
2.) What field size need to change to accommodate the size (Job_description)?
Thanks
>>The SQL query analyzer runs successfully but the output is not presented in a format structure for EXCEL<<
SQL Query Analyzer has three output formats: Text, Grid and File. There is no other option. If you can use Excel to import the results from a tab delimited file, than you can use that. Otherwise you will have to use something like DTS instead of SQL Query Analyzer.
>>What field size need to change to accommodate the size (Job_description)?<<
Only you know the answer to that question. If the maximum amount of data for each row is less than 8000 than use varchar(8000) otherwise you will have to resort to using text data type. The example you posted had a little over 1200 bytes.
SQL Query Analyzer has three output formats: Text, Grid and File. There is no other option. If you can use Excel to import the results from a tab delimited file, than you can use that. Otherwise you will have to use something like DTS instead of SQL Query Analyzer.
>>What field size need to change to accommodate the size (Job_description)?<<
Only you know the answer to that question. If the maximum amount of data for each row is less than 8000 than use varchar(8000) otherwise you will have to resort to using text data type. The example you posted had a little over 1200 bytes.
ASKER
1.) I will give DTS a shot.
2.) I will try to change to text data type and see what this provides.
Thank you.
2.) I will try to change to text data type and see what this provides.
Thank you.
>>I will try to change to text data type and see what this provides.<<
Be aware that although you can save a virtual unlimited amount of data in a text data type, it has some limitations in the SQL clauses and functons you can use.
Be aware that although you can save a virtual unlimited amount of data in a text data type, it has some limitations in the SQL clauses and functons you can use.
ASKER
2.) I used the DTS query functionality to resolve the EXCEL formatting issues I was experiencing, thanks for this insite.
2.) Here is what I recieve from SQL analyzer when INSERTING commas within the Job_description column records:
insert into job_title
(EEO_Classification, Job_title, Job_description, Exempt_status)
values ('Office2/Clerical2', 'Accounting Clerk2', 'Computes, classifies, records, and verifies numerical data for use in maintaining
accounting records.', 'NE');
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
I tried to change the data type of Job_description to text and the text parameter is not valid.
Should commas be valid characters when inserting data?
Thank you.
2.) Here is what I recieve from SQL analyzer when INSERTING commas within the Job_description column records:
insert into job_title
(EEO_Classification, Job_title, Job_description, Exempt_status)
values ('Office2/Clerical2', 'Accounting Clerk2', 'Computes, classifies, records, and verifies numerical data for use in maintaining
accounting records.', 'NE');
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
I tried to change the data type of Job_description to text and the text parameter is not valid.
Should commas be valid characters when inserting data?
Thank you.
>>I tried to change the data type of Job_description to text and the text parameter is not valid.<<
Post your Table structure and the command you used to change it.
Post your Table structure and the command you used to change it.
ASKER
update job_title
set Job_description = (varchar(100), Not Null)
where Job_description = (text (1200), Not Null);
Server: Msg 195, Level 15, State 10, Line 2
'varchar' is not a recognized function name.
Table job_title structure:
EEO_Classification CHAR (50),
Job_title VARCHAR (50),
Job_description VARCHAR (100),
Exempt_status CHAR (4),
PRIMARY KEY (Job_title)
Thanks.
set Job_description = (varchar(100), Not Null)
where Job_description = (text (1200), Not Null);
Server: Msg 195, Level 15, State 10, Line 2
'varchar' is not a recognized function name.
Table job_title structure:
EEO_Classification CHAR (50),
Job_title VARCHAR (50),
Job_description VARCHAR (100),
Exempt_status CHAR (4),
PRIMARY KEY (Job_title)
Thanks.
ASKER
I tried this syntax:
update job_title
set Job_description = 'varchar'
where Job_description = 'text'
The result was:
(0 row(s) affected)
Appear to have been successfull but when refresh table the Job_description still remained as varchar data type.
update job_title
set Job_description = 'varchar'
where Job_description = 'text'
The result was:
(0 row(s) affected)
Appear to have been successfull but when refresh table the Job_description still remained as varchar data type.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Since the text you posted had more than 1200 bytes, if you choose varchar you would be wise to set it greater than 1200, something like varchar(2000), but it really depends on your other rows and any potential growth.
ASKER
acperkins,
This executed correctly. Thank you for your patience and learning lessons, I appreciate it.
This executed correctly. Thank you for your patience and learning lessons, I appreciate it.
for 2: one of your fields you are trying to insert into can not support as much text ... field size has to be changed or your data shortened.