Inserting into TR_WEB_SOURCE_NO

I’ve done something similar to this by using the row_number() over (partition ….

First you would need to find the max(id) from TR_WEB_SOURCE_NO

For this example, let’s say it is 200.  The syntax for your query may look something like the below.

SELECT row_number() over (partition by a.password order by a.password) + 200,

    1412, 8, 'Y', a.password

FROM  CITIpasscodes$ as a

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Jesse Dillman
Sent: Friday, February 26, 2016 4:46 PM
To: Hoffmann, Tanya
Subject: [Tessitura Technical Forum] Inserting into TR_WEB_SOURCE_NO

For an upcoming promoter event, CITI credit card is providing us with an excel of some 2000 unique passcodes for their cardmembers to use to get early access to this event that they want on sale next week. We are hoping to talk them into using one single promo code that would be emailed to each member for access, but in the event we can't talk them into, I am preparing to get ou system ready to import these codes.

In TEST, I ran an import into the Impresario Database for the excel sheet they provided. I was able to successfully import it into a new table with a single column listed as Passwords. I then attempted an insert query to try and import those numbers all as unique promocodes into the TR_WEB_SOURCE_NO table as such:

INSERT INTO TR_WEB_SOURCE_NO (source_no, mos, override-ind, promo_code)
SELECT 1412, 8, 'Y', a.Passwords
FROM CITIpasscodes$ as a

When I ran the query I got an error stating that I could not insert a value of NULL into the ID column. I assume that since the ID is the primary key that it can't be null and my insert query is not auto incrementing values for this column.

I deleted the table and added a column to the excel file and inserted an integer into that column starting with the next available ID number from that system table, and increasing by 1 till the last number. I imported the table again and ran the query this time also trying to insert the ID value from my passcode table into the promo code table, but got the same exact error message. I have searched google for a long time on how to auto increment a primary key in an insert query but have not had any luck. Anyone have a better idea on how to do this, or is there an easier way to do something like this promoter is requesting?

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

I’ve done something similar to this by using the row_number() over (partition ….

First you would need to find the max(id) from TR_WEB_SOURCE_NO

For this example, let’s say it is 200.  The syntax for your query may look something like the below.

SELECT row_number() over (partition by a.password order by a.password) + 200,

    1412, 8, 'Y', a.password

FROM  CITIpasscodes$ as a

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Jesse Dillman
Sent: Friday, February 26, 2016 4:46 PM
To: Hoffmann, Tanya
Subject: [Tessitura Technical Forum] Inserting into TR_WEB_SOURCE_NO

For an upcoming promoter event, CITI credit card is providing us with an excel of some 2000 unique passcodes for their cardmembers to use to get early access to this event that they want on sale next week. We are hoping to talk them into using one single promo code that would be emailed to each member for access, but in the event we can't talk them into, I am preparing to get ou system ready to import these codes.

In TEST, I ran an import into the Impresario Database for the excel sheet they provided. I was able to successfully import it into a new table with a single column listed as Passwords. I then attempted an insert query to try and import those numbers all as unique promocodes into the TR_WEB_SOURCE_NO table as such:

INSERT INTO TR_WEB_SOURCE_NO (source_no, mos, override-ind, promo_code)
SELECT 1412, 8, 'Y', a.Passwords
FROM CITIpasscodes$ as a

When I ran the query I got an error stating that I could not insert a value of NULL into the ID column. I assume that since the ID is the primary key that it can't be null and my insert query is not auto incrementing values for this column.

I deleted the table and added a column to the excel file and inserted an integer into that column starting with the next available ID number from that system table, and increasing by 1 till the last number. I imported the table again and ran the query this time also trying to insert the ID value from my passcode table into the promo code table, but got the same exact error message. I have searched google for a long time on how to auto increment a primary key in an insert query but have not had any luck. Anyone have a better idea on how to do this, or is there an easier way to do something like this promoter is requesting?

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!