CODEWITHSUNDEEP
sql-server-2008
user1742469
user1742469

Reputation: 35

SQL Server Trigger To Copy Data From Columns Of Inserted Row In One Table To Rows Of Another Table

I have a webform that takes a users input into various fields and places them into a table as individual columns. I need to then take this row of data and copy the data in the columns into a another table as rows. The columns in the first table are associated with an id in the second table (the id is for other purposes not related to the tables and are a static set).

I'm having trouble wrapping my head around this so I'm sure my description up above was not very clear. Here is a simplified example:

Table 1 

street_address | city | state | zip

TRIGGER

Table 2 

street_address | 1  
city | 2  
state| 3  
zip | 4

If one of the columns in table 1 is left blank, it should not create a row in table 2 at all.

Again, the id's that are used in table 2 are already set (that is, every column from table 1 is going to matched up to a predetermined id in table 2).

Any help with this would be great. I'm sorry if I didn't explain it all to well. I will clear up questions as they arise.

Thank you.

Upvotes: 0

Views: 647

Answers (1)

supergrady
supergrady

Reputation: 1322

It sounds like your trigger needs to UNPIVOT from the INSERTED table into another table. Keep in mind that you will need to cast out all of the columns in the select list to the length of the longest column. Here is a SQL Fiddle

SELECT colName, value
FROM(
  SELECT 
     street_address
    ,CAST(city AS VARCHAR(100)) AS city
    ,CAST(state AS VARCHAR(100)) AS state
    ,CAST(zip AS VARCHAR(100)) AS zip
  FROM Table1
)AS p
UNPIVOT(
  value FOR colName IN(street_address, city, state, zip)
)AS unpvt

Upvotes: 2

Related Questions