input a table from another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

input a table from another table

Author

Topic

papershop
Starting Member

27 Posts

Posted - 2011-11-21 : 05:08:00

Dear ALL

i would like to ask you about inserting data from a table into another table

i have a table called temp_target

name sales value_jan value_feb value_mar value_apr year
sony ericson joni $1000 $3000 $5000 $3000 2011

i want to insert int into table master_target with struktur like :

name sales month year value
sony ericson joni jan 2011 $1000
sony ericson joni feb 2011 $3000
sony ericson joni mar 2011 $5000
sony ericson joni apr 2011 $3000

could you give me some advice of what kind of sintactx should i use to insert data from temp_target into master_target

thx b4

@papershop

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 05:12:23

[code]
INSERT INTO master_target
SELECT name,sales,REPLACE(period,'value_','') AS [Month],[year],[value]
FROM temp_target t
UNPIVOT([value] FOR period IN ([value_jan],[value_feb],[value_mar],[value_apr])
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

papershop
Starting Member

27 Posts

Posted - 2011-11-21 : 20:02:33

Dear visakhm

thx for your help, the script is work,
but can i ask you more?

i want to change the table value_jan, value_feb, value_mar, value_apr to 1, 2,3,4
i use REPLACE(period,'value_','') AS [Month], but i only can change 1 column for ex i use
REPLACE(period,'value_jan','1') AS [Month]

what should i do if i want to change another column?

thx b4

@papershop

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 01:10:29

quote:
Originally posted by papershop

Dear visakhm

thx for your help, the script is work,
but can i ask you more?

i want to change the table value_jan, value_feb, value_mar, value_apr to 1, 2,3,4
i use REPLACE(period,'value_','') AS [Month], but i only can change 1 column for ex i use
REPLACE(period,'value_jan','1') AS [Month]

what should i do if i want to change another column?

thx b4

@papershop

no need of doing REPLACE for each column. once you UNPIVOt you'll get all column names as values in another column to which you can apply REPLACE() like i showed. but if you want to get 1,2 etc instead of Jan,Feb etc you need a calandar table. do you have calendar table in your db?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources