[T-SQL] Import excel custom formatting cell to sql server 2008 |
- Import excel custom formatting cell to sql server 2008
- Add New column and Update column in same script!
- Merge between two tables in two different servers
- Summing Invoice Amounts ONCE and MULTIPLE Invoice Payments in same T-SQL Statement
| Import excel custom formatting cell to sql server 2008 Posted: 30 May 2013 11:05 PM PDT I have an excel sheet . having different data column One column having data likeEffiency=======70%80%50%Column format = [<0]"";0%when I importing data from excel to sql server this column shows blank values in sql server I am usingOPENROWSETSELECT Effiency FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=E:\BI\Excel_Source_File\CS_Source_File\Camp201201231.xlsx', 'SELECT * FROM [Detailed_report$B7:AP39]')I also use option IMEX=1 and used OPENDATASOURCE no change same issue.Is there anyway that I can transfer data from excel to SQL Server and formatting ignored during transformation.Urgent and thanks in advance.Regards,Naseer Ahmed |
| Add New column and Update column in same script! Posted: 30 May 2013 09:57 AM PDT Hi All,i have a script as following.IF COLUMNPROPERTY( OBJECT_ID('test2.dbo.Fldr'),'ColumnName','old_pk') IS NULL BEGIN ALTER Table2 ADD old_pk INT END ;UPDATE T2SET T2.ID = T1.ID ,T2.old_pk = T1.[fldr_id] FROM Table1 T1 INNER JOIN Table2 T2 ON T1.[ID] = T2.[ID]when i execute the following i get the following errorMsg 207, Level 16, State 1, Line 39Invalid column name 'old_pk'.Everything works ok when i run the alter and update separately. but i need to do this in the same script in one go.how do i solve this? |
| Merge between two tables in two different servers Posted: 30 May 2013 06:51 AM PDT Hi friends,I have following issue:I have one table called customer in Cust database in SQL SERVER 2008R2, which is my destination table.and Other table called customer(same name as above and also same fields) on the oracle server, which is my source table.I have access of read only in the table which is on the oracle server under the LINKED SERVER folder in SQL SERVER 2008R2.now I am confuse how can I do the incremental ETL,(insert , update and delete) between source and destination table either via Stored procudre or SSIS package.Please help me. |
| Summing Invoice Amounts ONCE and MULTIPLE Invoice Payments in same T-SQL Statement Posted: 30 May 2013 05:11 AM PDT A master table contains a series of invoices and a detail table comprise, for any invoice, 1 or 2 or 3 ... payments for the invoice and an invoice also might have no payments. Obviously, if an invoice has multiple payments, the LEFT OUTER JOIN between the master and detail table will produce multiple records for the same invoice master. So the sum of the Amounts includes several repetition of the same invoice amount. And, of course, two distinct invoices may have the same amount.Been racking my brains to fabricate a SINGLE T-SQL that could simultaneously create the sum of each invoice amount and the sum of all the payments. These results have to be broken down by currency.Any ideas ?This is the required result:[code="plain"][font="Courier New"]/*GrandTotal Desired is the correct value, GrandTotal Obtained is wrongCurrency GrandTotal GrandTotal Payments Desired ObtainedEUR 15346.00 21349.00 800.80USD 6134.00 7135.00 667.3334*/[/font][/code]Here are table creation and filling scripts and the obviously failed LEFT OUTER JOIN[code="sql"][font="Courier New"]BEGIN TRANCREATE TABLE #INV( InvNo int NOT NULL IDENTITY, Amount money, Currency CHAR(3))CREATE TABLE #Pay( PayNo int NOT NULL IDENTITY(100,1), InvNo int NOT NULL, Paid money)INSERT INTO #INV (Amount, Currency)SELECT 1001, 'USD' UNIONSELECT 1011, 'USD' UNIONSELECT 1111, 'USD' UNIONSELECT 2001, 'EUR' UNIONSELECT 2011, 'EUR' UNIONSELECT 2111, 'EUR' UNIONSELECT 3001, 'EUR' UNIONSELECT 3011, 'USD' UNIONSELECT 3111, 'EUR' UNION ALLSELECT 3111, 'EUR' INSERT INTO #Pay (InvNo, Paid) SELECT TOP 1 InvNo, CONVERT(money, Amount/3.00) FROM #INVINSERT INTO #Pay (InvNo, Paid) SELECT TOP 1 InvNo, CONVERT(money, Amount/3.00) FROM #INV INSERT INTO #Pay (InvNo, Paid) SELECT TOP 1 InvNo+3, CONVERT(money, Amount/5.00) FROM #INV INSERT INTO #Pay (InvNo, Paid) SELECT TOP 1 InvNo+3, CONVERT(money, Amount/5.00) FROM #INV INSERT INTO #Pay (InvNo, Paid) SELECT TOP 1 InvNo+3, CONVERT(money, Amount/5.00) FROM #INV INSERT INTO #Pay (InvNo, Paid) SELECT TOP 1 InvNo+3, CONVERT(money, Amount/5.00) FROM #INV SELECT * FROM #INVSELECT * FROM #PaySELECT * FROM #INV LEFT OUTER JOIN #Pay ON #Pay.InvNo = #INV.InvNoSELECT #INV.Currency, SUM(#INV.Amount) AS GrandTotalFROM #INV GROUP BY #INV.CurrencySELECT #INV.Currency, SUM(#PAY.Paid) AS PaymentsFROM #Pay INNER JOIN #INV ON #Pay.InvNo = #INV.InvNoGROUP BY #INV.CurrencySELECT #INV.Currency, SUM(#INV.Amount) AS GrandTotal, SUM(#PAY.Paid) AS PaymentsFROM #INV LEFT OUTER JOIN #Pay ON #Pay.InvNo = #INV.InvNoGROUP BY #INV.CurrencyROLLBACK TRAN[/font][/code] |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment