Demographics Data Append

Hi Brian,

Just wanted to check whether you had a DBA there or somebody who could do SQL? If you do, then it's a simple job, as per Travis' post. If not, then I could understand it would take a lot longer.

If you have to do it via the utility, it will take a lot longer because of the one value at a time reason you mentioned.

If you don't have SQL expertise in the company, it might be worth checking with your company whether they could bring in Tessitura Consulting to import the information into your database for you. It wouldn't be difficult for them as long as you laid the information out neatly in a spreadsheet for them and paying them for a short job might be more effective than spending hours of your own?

Thank you both, Travis and Matthew!

I am vaguely intimated with SQL and have been begging, borrowing, and stealing to scrimp together what I think I need. I’ve got my spreadsheet imported into SQL and a generous colleague has even lent me some code he's used for a similar sort of task. I fear, however, that the code exceeds my grasp just a bit, as I have tended to do very very simple queries thus far in life. I’m wondering if you all might be able to help me connect the dots ...

So, I’ve got my spreadsheet imported as a table called “Append_Work_File,” and I’ve been able to confirm via a separate query (good old “SELECT *”) that the data is looking good. I’ve got the lent code, below—

create table #work
(
customer_no int not null,
keyword_no int not null, -- ID number for attribute from t_keyword
key_value varchar(30) not null -- Individual attribute value.
)
 
use impresario
 
/********************************************************************************************
Basic insert statement for tx_cust_keyword
You must have values in customer_no, keyword_no and key_value. Each row must be unique.
*********************************************************************************************/
 
execute as user = 'your user name' --optional to flag rows as being created by this user
 
insert      dbo.TX_CUST_KEYWORD
            (customer_no, keyword_no,  key_value)
select      customer_no, keyword_no, key_value
from  #work
 
 
revert; -- if you execute as somebody run this.

And this point, I’m sort of getting what this query is wanting to do, but what I’m failing to connect is the data I just imported to the process. I feel like what I need to do is something like this (question mark?)—

insert into #work (customer_no)
select customer_no
from append_work_file

And so on, based upon a spreadsheet that has a keyword_no column and then a key_value column that correspond with what I build in Tessitura for the attribute, correct? I can’t help but feel like there’s something I’m missing here, but maybe that’s my ignorance trying to quash enlightenment!

Hey Brian, I can help you out. What are the columns in the Append_work_file? Is there on row per patron?

Travis

Thank you, Travis! I actually ended up figuring it out, here's my process in the hopes that it's helpful for someone else out there trying to do the same thing (or if someone can improve upon it, don't hold back):

1.) Build all keywords according to Tessitura per the Tessitura resource page here.

2.) Once you have your attributes built, import a list per Attribute (not key_value), making sure that you have column headers "customer_no", "keyword_no", and "key_value". The first column is of course the customer ID, second keyword number (found in T_KEYWORD), and finally the third is the value you wish to insert into the respective constituent record. I had some guidance from my friends around town on this one, but you can also find a general primer here.

3.) Use the following code in SQL to populate the Attribute (in TEST first, at least for the lesser dare devils of the world):

create table #work
(
customer_no int,
keyword_no int, -- ID number for attribute from t_keyword
key_value varchar(30)  -- Individual attribute value.
)

insert into #work (customer_no, keyword_no, key_value)
select customer_no, keyword_no, key_value
from Your_Imported_Table_Title_Here                              ------- Placeholder for your newly imported data table name

use impresario
 
/********************************************************************************************
Basic insert statement for tx_cust_keyword
You must have values in customer_no, keyword_no and key_value.
*********************************************************************************************/
 
insert      dbo.TX_CUST_KEYWORD
            (customer_no, keyword_no,  key_value)
select      customer_no, keyword_no, key_value
from  #work

drop table #work

Lather, rinse, and repeat for each attribute that you're adding. This has enabled me to append a variety of key values for any given attribute without having to import one value at a time.

Things to remember--

1.) Sometimes, when you're importing an Excel spreadsheet into SQL, you may have to disconnect and reconnect Object Explorer for your new table to show up. At least I did. Annoying, but not a deal breaker.

2.) Don't forget to designate all the necessary constituencies in TX_KEYWORD_CUST_TYPE for each keyword! I had a whole swath of constituents that my insertion seemed to be missing ... This was the cause.

3.) When you're swapping out codes from Experian for actual ethnicity titles, don't allow a Freudian slip of frustration for you to inadvertently replace "Scottish" with "Scotch". I plead the 5th.

Thank you, Travis! I actually ended up figuring it out, here's my process in the hopes that it's helpful for someone else out there trying to do the same thing (or if someone can improve upon it, don't hold back):

1.) Build all keywords according to Tessitura per the Tessitura resource page here.

2.) Once you have your attributes built, import a list per Attribute (not key_value), making sure that you have column headers "customer_no", "keyword_no", and "key_value". The first column is of course the customer ID, second keyword number (found in T_KEYWORD), and finally the third is the value you wish to insert into the respective constituent record. I had some guidance from my friends around town on this one, but you can also find a general primer here.

3.) Use the following code in SQL to populate the Attribute (in TEST first, at least for the lesser dare devils of the world):

create table #work
(
customer_no int,
keyword_no int, -- ID number for attribute from t_keyword
key_value varchar(30)  -- Individual attribute value.
)

insert into #work (customer_no, keyword_no, key_value)
select customer_no, keyword_no, key_value
from Your_Imported_Table_Title_Here                              ------- Placeholder for your newly imported data table name

use impresario
 
/********************************************************************************************
Basic insert statement for tx_cust_keyword
You must have values in customer_no, keyword_no and key_value.
*********************************************************************************************/
 
insert      dbo.TX_CUST_KEYWORD
            (customer_no, keyword_no,  key_value)
select      customer_no, keyword_no, key_value
from  #work

drop table #work

Lather, rinse, and repeat for each attribute that you're adding. This has enabled me to append a variety of key values for any given attribute without having to import one value at a time.

Things to remember--

1.) Sometimes, when you're importing an Excel spreadsheet into SQL, you may have to disconnect and reconnect Object Explorer for your new table to show up. At least I did. Annoying, but not a deal breaker.

2.) Don't forget to designate all the necessary constituencies in TX_KEYWORD_CUST_TYPE for each keyword! I had a whole swath of constituents that my insertion seemed to be missing ... This was the cause.

3.) When you're swapping out codes from Experian for actual ethnicity titles, don't allow a Freudian slip of frustration for you to inadvertently replace "Scottish" with "Scotch". I plead the 5th.

Awesome!