Import Excel with Openrowset



 
if object_id('[aNewTable20150729]') IS NULL

Begin
CREATE TABLE [dbo].[aNewTable20150729](
	[accountnumber] [nvarchar](255) NULL,
	[COl1] [nvarchar](2550) NULL
) 
End

Insert into aNewTable20150729

SELECT * FROM OPENROWSET('MSDASQL','DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);
DBQ=c:\temp\test8.xlsx','SELECT * FROM [Sheet1$]') 

select *, len(COL1)  from aNewTable20150729

truncate table aNewTable20150729


 
Advertisements

Import and export between Excel file and SQL Server table by using Powershell script

Some sample codes from Simple Talk website to show how to import and export between Excel file and SQL Server table.
Here is the link:

https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc–/


Dense_Rank with Gaps and Islands Problem–Another Solution

  

 CREATE TABLE #CustomerOffer ( id INT, qid INT, Referralid INT, Offerid INT, OfferAmount INT)

INSERT INTO #CustomerOffer VALUES ( 1,421,513452,101,10000)
INSERT INTO #CustomerOffer VALUES ( 2,421,513452,102,10001)
INSERT INTO #CustomerOffer VALUES ( 3,421,513452,103,10002)
INSERT INTO #CustomerOffer VALUES ( 4,421,513452,104,10003)
INSERT INTO #CustomerOffer VALUES ( 5,421,513452,105,10004)

INSERT INTO #CustomerOffer VALUES ( 6,421,5290373,3234,10010)
INSERT INTO #CustomerOffer VALUES ( 7,421,5290373,3284,100011)
INSERT INTO #CustomerOffer VALUES ( 8,421,5290373,3923,100022)

INSERT INTO #CustomerOffer VALUES ( 9,422,513454,1111,100101)
INSERT INTO #CustomerOffer VALUES ( 10,422,513454,1112,1000111)
INSERT INTO #CustomerOffer VALUES ( 11,422,513454,1113,1000222)



select Id, qid,ReferralId, OfferId, OfferAmount, 
dense_rank() over(order by Cast(qid as binary(4))+ Cast(ReferralId as binary(4))) as ReferralRnk 
,ROW_NUMBER() over (partition by qid, referralId order by OfferId) as PgmNbr

From #CustomerOffer
order by Id


drop table #CustomerOffer
 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/423886f8-9d54-45ee-a12d-b9de62f83e8c/tsql-sorting-help?forum=transactsql


Run Large SQL script for multiple Inserts with Sqlcmd

You can use sqlcmd utility to run T-SQL statements or sql scripts. When you have difficulty to run large sql statements in SSMS, you can try to run the script through a command window or Powershell window.

The sqlcmd Utility’s official documentation page:

https://msdn.microsoft.com/en-us/library/ms162773.aspx?f=255&MSPPError=-2147217396

The file size of datascript.sql is about 100M and it has multiple rows of insert command generated through Visual Studio Data Comparison result.
Some of the Inserts content in my SQL script file have the value $, so I need to use the disable variable substitution flag -x to disable the variable swap.

The final script looks like this:

  
 
sqlcmd -S MC\MSSQL2014 -i "C:\temp\datascript.sql" -x -o "C:\temp\outputfile.txt"

 
 

The script can be run through Powershell or cmd window.
An easy way to run this script is from Visual Studio to use teh Update Target command, but I need a script to share with other developers for these inserts.


Week start for the past 5 weeks with custom start date (Monday)

  
 SET DATEFIRST 1
 declare @enddate date=getdate()
declare @startdate date=DATEADD(week, DATEDIFF(week,0,dateadd(week,-5,@enddate)),-1)


;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
 
,dates as (
Select datepart(week, dateadd(day,n-1,@startdate) ) wk, dateadd(day,n-1,@startdate) dt   from Nums
WHERE dateadd(day,n-2,@startdate)< @enddate
)
 


 Select wk,     Max(Case when datename(dw,dt)='Monday' Then dt End) weekstart,
 Max(Case when datename(dw,dt)='Sunday' Then dt End) weekend  from dates
Group by wk
Having( Max(Case when datename(dw,dt)='Monday' Then dt End) is not null 
 and Max(Case when datename(dw,dt)='Sunday' Then dt End) is not null)


 SET DATEFIRST 7
 
 

Find out unique counts based on consecutive days

  

Declare @Test TABLE (id int,[Date] date)


INSERT INTO @Test Values
(100,'07/01/2015')
,(100,'07/02/2015')
,(100,'07/03/2015')
,(100,'08/01/2015')
,(100,'08/02/2015')
,(100,'08/15/2015')  

;with mycte as (
select   DATEADD(DAY, - ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Date]) ,[Date]) grp 
from @Test)

Select Count(Distinct grp) cnt 
from mycte



--3
 

https://social.msdn.microsoft.com/Forums/en-US/0a019eac-016e-44da-bc6f-d278628d27f2/find-out-unique-counts-based-on-consecutive-days?forum=transactsql


NTILE with a variable sample

  


-- Create Sample Table

Declare @GroupRelation_Test TABLE (
	MD_Group [varchar](20),
	CustomerNumber [varchar](20) , 
	AmountBilled [varchar](20) ,
    MinAmounttBilled [varchar](20) 
)



-- Test data

INSERT INTO @GroupRelation_Test

      ( CustomerNumber, AmountBilled,MinAmounttBilled)

SELECT		'12',	'15243'	,'' UNION ALL
SELECT		'1231234',	'15243'	,'' UNION ALL
SELECT		'463',	'15243'	,'' UNION ALL
SELECT		'442',	'15243'	,'' UNION ALL
SELECT		'4324',	'15243'	,'' UNION ALL
SELECT		'3123',	'15243'	,'' UNION ALL
SELECT		'67123',	'15243'	,'' UNION ALL
SELECT		'109988',	'15243'	,'' UNION ALL
SELECT		'74665',	'15243'	,'' UNION ALL
SELECT		'23323',	'15243'	,'' UNION ALL
SELECT		'9866',	'15243'	,''  
 


Declare @intForNtile int
  Set @intForNtile = Ceiling((Select count(*) from @GroupRelation_Test)/ 3.0) 
  --Make 3 as decimal 

select ntile(@intForNtile) Over(Order by newid()) MD_Group
, * from @GroupRelation_Test
 

https://social.msdn.microsoft.com/Forums/en-US/74ed0a9f-2ca4-4d97-9ce6-d95cf1528819/grouping-data-within-a-db-table?forum=transactsql