new field id

Question

new field id

gxdatareal

Ten Centuries

Points: 1022
More actions
May 13, 2016 at 7:35 am

#307556

Hi
I need help for an SSIS problem
I Have a table with some fields like title, price , name and so on
The data of this table is
title, price , name
mars,10,john
mars,10,john
space ,12,alex
I want to add an id (using SSIS or any other idea) in front of title but for the same title should be the same id like
Id,title, price , name
111111,mars,10,john
111111,mars,10,john
234567, space ,12,alex
How can I do it

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

dndaughtery SSChampion Points: 11290 More actions · Answer 1

Since the table already exists you'd need to create another table and add the id column then load it with the data from the old table, delete the old table, and rename the new table to the original old tables name.

gxdatareal Ten Centuries Points: 1022 More actions · Answer 2

??. but I want the numbers of the id to be written automatically and not manualy. lets assume i have a table with 10,000 rows its not possible to do it mannually.

i want a process to add the id in the field automatically (and as i said it should be the same id when the title is same)

probably something like Generate an Auto Incremental Number in a SSIS Package but with the restriction that for the same tile i need the same number (id)

dndaughtery SSChampion Points: 11290 More actions · Answer 3

Set the column to be an Identity(1,1). Means that the first row will have id value of 1 and the next will be 2, and on and on.

gxdatareal Ten Centuries Points: 1022 More actions · Answer 4

i do not want to generate an increment but when there is same title should be the same id

dndaughtery SSChampion Points: 11290 More actions · Answer 5

Wait a sec, just reread your original post. You want the same id for common titles. Seems like you should have a lookup table. You could select distinct title into another table with an Identity(1,1) id column. then you could run an update joining the two tables on the title updating the id column sort of like below:

Update A

set id = b.id

from TableA as A inner join TableB as B

On A.title = B.title

Lowell SSC Guru Points: 323504 More actions · Answer 6

a row_number() in a view might be a solution, since he wants a repeating value.

i think i would just a dd a calculated column that does the checksum; that would work.

/*

IDtitlepricename

599888mars10john

11098002space12alex

*/

;WITH MyCTE([title],[price],[name])

AS

(

SELECT 'mars','10','john' UNION ALL

SELECT 'space','12','alex'

)

SELECT CHECKSUM(title) As ID,* FROM MyCTE;

--ALTER TABLE MyTable ADD [ID] AS CHECKSUM(title) PERSISTED

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dndaughtery SSChampion Points: 11290 More actions · Answer 7

Or you could use a lookup task and using a select distinct title along with row_number() to create the id to get the lookup data and then join on title

gxdatareal Ten Centuries Points: 1022 More actions · Answer 8

gxdatareal

Ten Centuries

Points: 1022

May 13, 2016 at 10:28 am

#1878974

thanks. it works