i have a data in microsoft excel which always come columns and the columns are many (like 50 columns or more) which i need to update a particular column in the database table. which script can i used to accomplished this task to update this column in my table? E.g code basic_salary, meal_subsidy, Entertainment, Transport, Rent_Subsidy, Callduty 45625 60000 3000 2500 4000 1000 4000 and the destination table looks like this: **Code,** **Item_type**, **Rate** 45625 basic_salary 60000 45625 Meal_subsidy 3000 45625 Entertainment 2500 45625 Transport 4000 45625 Rent_Subsidy 1000 45625 Callduty 4000 This data is much, and it comes very often, how can i achieved this.
I would use Excel to format the data in the right way as it allows for a range of data to be copied and pasted transposed (use Paste Special from right click options). Once the data is fixed then there are loads of ways to import it, OPENROWSET, using linked server, bcp, LogParser and so on. I guess the details behind "much" and "often" will dictate the best option to use.
DECLARE @test TABLE ( Code INT, basic_salary INT, meal_subsidy INT, Entertainment INT, Transport INT, Rent_Subsidy INT, Callduty INT); INSERT INTO @test ([code], [basic_salary], [meal_subsidy], [Entertainment], [Transport], [Rent_Subsidy], [Callduty]) VALUES (45625, 60000, 3000, 2500, 4000, 1000, 4000); And the useful bit SELECT [Code], [Item_type], [Rate] FROM @test UNPIVOT (Rate FOR Item_type IN ([basic_salary], [meal_subsidy], [Entertainment], [Transport], [Rent_Subsidy], [Callduty])) u You haven't really said what SQL Server version you're on - this is 2005+ Just for Thomas - here's the dynamic version. Note that it has to be in a real table for access to the meta-data. CREATE TABLE #test ( Code INT, fred INT, likes INT, chocolate INT, ice_cream INT, for_his INT, breakfast INT); INSERT INTO #test ([code], [fred], [likes], [chocolate], [ice_cream], [for_his], [breakfast]) VALUES (45625, 60000, 3000, 2500, 4000, 1000, 4000); DECLARE @sql [nvarchar] (MAX) SET @sql = 'SELECT [Code], [Item_type], [Rate] FROM #test UNPIVOT (Rate FOR Item_type IN (' + stuff((SELECT ', ' + QUOTENAME([name]) FROM tempdb.sys.columns WHERE [object_id] = OBJECT_ID('tempdb..#test') AND [name] != 'Code' for xml path(''), TYPE).value('.','varchar(max)'), 1, 2, '') + ')) u' EXEC (@sql) DROP TABLE #test