CODEWITHSUNDEEP
sql-serversql-server-2008
RUCK FULES
RUCK FULES

Reputation: 249

How to add data to a single column

I have a question in regards to adding data to a particular column of a table, i had a post yesterday where a user guided me (thanks for that) to what i needed and said an update was the way to go for what i need, but i still can't achieve my goal.

i have two tables, the tables where the information will be added from and the table where the information will be added to, here is an example:

source_table  (has only a column called "name_expedient_reviser" that is nvarchar(50))
name_expedient_reviser
kim
randy
phil
cathy
josh
etc.

on the other hand i have the destination table, this one has two columns, one with the ids and the other where the names will be inserted, this column values are null, there are some ids that are going to be used for this.

this is how the other table looks like

dbo_expedient_reviser (has 2 columns, unique_reviser_code numeric PK NOT AI, and name_expedient_reviser who are the users who check expedients this one is set as nvarchar(50)) also this is the way this table is now:

dbo_expedient_reviser
unique_reviser_code | name_expedient_reviser 
1                   | NULL
2                   | NULL
3                   | NULL
4                   | NULL
5                   | NULL
6                   | NULL

what i need is the information of the source_table to be inserted into the row name_expedient_reviser, so the result should look like this

dbo_expedient_reviser
unique_reviser_code | name_expedient_reviser 
1                   | kim
2                   | randy
3                   | phil
4                   | cathy
5                   | josh
6                   | etc.

how can i pass the information into this table? what do i have to do?.

EDIT

the query i saw that should have worked doesn't update which is this one:

UPDATE dbo_expedient_reviser
SET dbo_expedient_reviser.name_expedient_reviser = source_table.name_expedient_reviser
FROM source_table
JOIN dbo_expedient_reviser ON source_table.name_expedient_reviser = dbo_expedient_reviser.name_expedient_reviser
WHERE dbo_expedient_reviser.name_expedient_reviser IS NULL

the query was supposed to update the information into the table, extracting it from the source_table as long as the row name_expedient_reviser is null which it is but is doesn't work.

Upvotes: 1

Views: 985

Answers (2)

RUCK FULES
RUCK FULES

Reputation: 249

it is not posible to INSERT the data into a single column, but to UPDATE and move the data you want is the only way to go in that cases

Upvotes: 0

Nick A
Nick A

Reputation: 126

Since the Names do not have an Id associated with them I would just use ROW_NUMBER and join on ROW_NUMBER = unique_reviser_code. The only problem is, knowing what rows are null. From what I see, they all appear null. In your data, is this the case or are there names sporadically in the table like 5,17,29...etc? If the name_expedient_reviser is empty in dbo_expedient_reviser you could also truncate the table and insert values directly. Hopefully that unique_reviser_code isn't already linked to other things.

WITH CTE (name_expedient_reviser, unique_reviser_code)
AS
    (
    SELECT   name_expedient_reviser
            ,ROW_NUMBER() OVER (ORDER BY name_expedient_reviser)
    FROM source_table
    )
UPDATE er
SET er.name_expedient_reviser = cte.name_expedient_reviser      
FROM dbo_expedient_reviser er
JOIN CTE on cte.unique_reviser_code = er.unique_reviser_code

Or Truncate:

Truncate Table dbo_expedient_reviser

INSERT INTO dbo_expedient_reviser (name_expedient_reviser, unique_reviser_code)
SELECT DISTINCT
    unique_reviser_code = ROW_NUMBER() OVER (ORDER BY name_expedient_reviser)
   ,name_expedient_reviser
FROM source_table

Upvotes: 1

Related Questions