The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Importing data into a new table
Posted: 28 Nov 07 1:33 PM
|
Forgive me in advance if this doesn't make sense. I am new to this and the guy that was trained for this is no longer with the company so it falls to me.
I am trying to import an access table into saleslogix. This is a one time event and the data will never change. We are on version 6.2.6.1007 and the data is going to be used for a report in crystal reports. We also have several remote users so I am not sure how to go about setting up this table and having go out to the remotes. Any help would be greatly appreciated!
Thanks in advance,
Renee |
|
|
|
Re: Importing data into a new table
Posted: 28 Nov 07 5:20 PM
|
The new (V7) Import Wizard will do this I know - and it takes care of sync - but I haven't used the Pre-V7 one for a while and cannot remember if it is flexible enough to do non-Account/Contact stuff.
One option is to create a huge INSERT INTO query, containing all the data you want to insert and then run this through
Tools/Execute SQL
in the Administrator client. Use Access or Excel to generate the query script for you. One caveat: there may be a limit on how long this query can be, but I do not know it.
Or (maybe the most elegant way) you can use DTS/SSIS to pump the data into the main db using the OLEDB provider to connect.
PP |
|
|
|
Re: Importing data into a new table
Posted: 12 Jun 08 8:30 AM
|
Thank you for your help. I have created an excel spreadsheet with the data that I need to import and I have been able to create the INSERT INTO query. MY question now is when I run this in SQL in the Administrator client, will it or how does it know to generate the new record id for each new record? Or is that something that I have to code into my Insert statement?
Thank you in advance!
Renee |
|
|
|
Re: Importing data into a new table
Posted: 12 Jun 08 5:24 PM
|
It will work, as long as the AUTO INCREMENT property of the table's ID field is set to true. This can be checked by viewing the properties of the field through db manager.
For example, the following statement
insert into test(text) values('test')
could be run to insert a single record into table 'Test' (if such a table existed) with a single additional field called 'text' - assuming Test.TestID has auto increment set to True.
All of the following standard fields would be populated automatically by the provider (assuming you're on a recent SLX release):
TestID
CreateUser
CreateDate
ModifyUser
ModifyDate
Phil
|
|
|
|
Re: Importing data into a new table
Posted: 13 Jun 08 7:31 AM
|
Thank you for your help. When I enter my Insert statement in SQL and test it, I get the following error message:
The statement failed and retruned the following error: "The statement has been terminated.: Cannot insert the value NULL into column 'Account_ShipfromID', table 'saleslogix_dev.sysdba.Account_ShipFrom'; column does not allow nulls. INSERT fails."
What am I doing wrong? 
I have checked the properties for that field and I do have AutoIncrement checked. I am running saleslogix version 6.2.6.
Thanks,
Renee |
|
|
| |
|
Re: Importing data into a new table
Posted: 13 Jun 08 12:13 PM
|
Thank you for your response. I should have been clearer before. I go into the Saleslogix Administrator, open Database Manager and then click the SQL button. When the Execute SQL box appears I right click to Add a SQL statement and then enter my Insert statement. When I click Test, I get that error. Am I missing something else. Do I have to add some other code for the OLEDB Provider?
Thanks,
Renee |
|
|
|
Re: Importing data into a new table
Posted: 13 Jun 08 1:06 PM
|
Set operations will not work with the OLEDB provider and the auto increment feature. In other words, you can only insert one record at a time. Since you are using executesql I suspect you are using an INSERT INTO ... SELECT Columns FROM Source approach. Unless your source has the primary key already generated this will not work.
Timmus |
|
|
|
Re: Importing data into a new table
Posted: 13 Jun 08 1:29 PM
|
No. This is the INSERT statement that I was using:
INSERT INTO ACCOUNT_SHIPFROM(SHIP_FROM_NO,SFNAME,ADDRESS1,ADDRESS2,CITY,STATE,POSTALCODE,MILEAGE,MINIMUM_MILEAGE,COUNTY_CODE,CITY_CODE,COUNTRY) VALUES( 1,'TEST CUSTOMER','30 TEST DRIVE','(THIS IS A TEST)','GRAFTON', 'OH', '44044', 1,' ', ' ', ' ', 'USA')
Thanks!
Renee |
|
|
|
Re: Importing data into a new table
Posted: 13 Jun 08 1:43 PM
|
If you have configured the autoincrement feature correctly it may be that the SalesLogix Server services need to be restarted to recognize the metadata change. Otherwise I am not certain why it would not work as your code looks fine to me.
Since this data is not editable you may want to just generate the primary key in Excel. I would not worry about making these "SalesLogix IDs" simply use the Excel Fill | Series feature to generate sequential numeric values. Then insert those values as your primary key in SalesLogix.
This is certainly a hack but if this is only a reference table that wont be modified within SalesLogix, I dont see the harm.
Timmus |
|
|
|
Re: Importing data into a new table
Posted: 13 Jun 08 2:15 PM
|
TImmus,
Thank you, thank you, thank you!!! If you were here I would give you a big hug!!!!
Restarting the Saleslogix Server was the answer. I can now complete this task and move on. This has been a thorn for me for days now.
 
Thanks again to everyone at slxdeveloper. I don't know what I would do without your knowledge and support!!
Renee |
|
|
|