Insert into Three Linking Tables

http://forums.asp.net/p/2102281/6076257.aspx?p=True&t=636078096225251552

  

  CREATE TABLE [dbo].[tCustomers] (
    [CustID]  INT        IDENTITY (1, 1) NOT NULL,
    [CustName] NCHAR (10) NOT NULL,
    PRIMARY KEY CLUSTERED ([CustID] ASC)
);

CREATE TABLE [dbo].[tSalesOrders] (
    [SOID]         INT        IDENTITY (1, 1) NOT NULL,
    [CustID]       INT        NOT NULL,
    [SODescription] NCHAR (10) NOT NULL,
    PRIMARY KEY CLUSTERED ([SOID] ASC)
);

CREATE TABLE [dbo].[tSalesItems] (
    [SIID]         INT        IDENTITY (1, 1) NOT NULL,
    [SOID]         INT        NOT NULL,
    [SIDescription] NCHAR (10) NOT NULL,
    PRIMARY KEY CLUSTERED ([SIID] ASC)
);

 USE [mydb1]
GO
/****** Object:  StoredProcedure [dbo].[spCopyCustomer]    Script Date: 8/26/2016 2:03:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCopyCustomer]
    @CustID AS INT   /* CustID of the customer we want to copy */
AS
BEGIN

   --add an extra column
 IF COL_LENGTH('dbo.tSalesOrders','OlDSOID') IS NULL
 BEGIN
 ALTER TABLE dbo.tSalesOrders ADD OlDSOID INT;
 END;

DECLARE @NewCustID AS INT;
Create table #tmpCustID   (SOID int, OldCustID INT, OlDSOID2 INT); 

INSERT INTO tCustomers (CustName)
  SELECT CustName FROM tCustomers
    WHERE tCustomers.CustID = @CustID;

--Get the new inserted CustID
Select @NewCustID=scope_identity()

declare @sql nvarchar(4000)= N'INSERT INTO tSalesOrders (CustID, SODescription,OlDSOID)
OUTPUT  INSERTED.SOID,'+  Cast(@CustID as nvarchar(30))+',  INSERTED.OlDSOID INTO #tmpCustID(SOID, OldCustID, OlDSOID2)
SELECT '+ Cast(@NewCustID as nvarchar(30))  +', SODescription, SOID FROM tSalesOrders WHERE CustID = '+ Cast(@CustID as nvarchar(30))

 EXEC sp_executesql @sql 

INSERT tSalesItems (SOID, SIDescription)
Select  t.SOID, si.SIDescription
from #tmpCustID t join tSalesOrders so on t.OlDSOID2=so.SOID join tSalesItems si on si.SOID=so.SOID
WHERE t.OldCustID=@CustID

   --drop the extra column
 IF COL_LENGTH('dbo.tSalesOrders','OlDSOID') IS Not NULL
 BEGIN
 ALTER TABLE dbo.tSalesOrders Drop column OlDSOID;
 END;

End

--Another approach:

ALTER PROCEDURE [dbo].[spCopyCustomer2]
    @CustID AS INT   /* CustID of the customer we want to copy */
AS
BEGIN

--Create a temp table
CREATE TABLE #tSalesOrders2 (id INT IDENTITY (1, 1) NOT NULL, [SOID] INT,[SOID_old] INT );

 --Insert table customer
INSERT INTO tCustomers (CustName)
  SELECT CustName FROM tCustomers
    WHERE tCustomers.CustID = @CustID;

 --Merge to table tSalesOrders for new customer
 Merge tSalesOrders tgt
 using (
Select  SODescription,	SIID,	SIDescription,	so.SOID as SOID_old, CustName, c.CustID, scope_identity() as newCustID
, row_number() Over(partition by so.SOID order by so.SOID) rn
from  tSalesOrders so join tSalesItems si on si.SOID=so.SOID
join tCustomers c on c.CustID=so.CustID
WHERE c.CustID=@CustID
 ) as src on 1=2
When not matched  and src.rn=1 then
Insert (CustID,SODescription) values(src.newCustID,src.SODescription)
OUTPUT inserted.SOID, src.SOID_old
into  #tSalesOrders2 (SOID, SOID_old);

---Insert  to tSalesItems with new SOID
insert into tSalesItems (SOID,SIDescription)
select so2.SOID,si.SIDescription
 from tSalesItems si join #tSalesOrders2 so2 on si.SOID=so2.SOID_old

   --select * from tSalesOrders
   --select * from tSalesItems 

End
 

Remove Leading Zeros in a Varchar Column but keep inside spaces

 create table test (col varchar(100))
insert into test values('00876H873 '),('00876 876500'),('0000HUJ8 9IU877 8UJH')

select * ,   LTRIM(Replace(REPLACE(LTRIM(REPLACE(Replace(col, ' ','*'),'0', ' ')), ' ', '0'),'*',' ')) AS NumSerie from test

drop table test

--https://social.msdn.microsoft.com/Forums/en-US/7ece2a11-efac-45b6-97af-af1873788bb0/tsql-remove-leading-zeroes-in-a-string?forum=transactsql

 

Get file information and save to a table with Powershell

Function AutoImportCommaFlatFiles($location, $file, $extension, $server, $database)
{
    $full = $location + $file + $extension
    $all = Get-Content $full
    $columns = $all[0]
    $columns = $columns.Replace(" ","")
    $columns = $columns.Replace(",","] VARCHAR(100), [")
    $table = "if object_id( '"+ $file + "','U')is not null drop table " + $file + " CREATE TABLE " + $file + "([" + $columns + "] VARCHAR(100))"
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $buildTable = New-Object System.Data.SqlClient.SqlCommand
    $insertData = New-Object System.Data.SqlClient.SqlCommand
    $connection.ConnectionString = "Data Source=" + $server + ";Database=" + $database + ";integrated security=true"
    $buildTable.CommandText = $table
    $buildTable.Connection = $connection
    ## Added to function
    $x = 0
    $insertData.CommandText = "EXECUTE stp_CommaBulkInsert @1,@2"
    $insertData.Parameters.Add("@1", $full)
    $insertData.Parameters.Add("@2", $file)
    $insertData.Connection = $connection
    $connection.Open()
    $buildTable.ExecuteNonQuery()
    $connection.Close()
    ## Added to function
    $x = 1
    if ($x = 1)
    {
        $connection.Open()
        $insertData.ExecuteNonQuery()
        $connection.Close()
    }
}




## export fil info to a csv file
$myexport="c:\temp\mypdffiles.csv"
 $mypath="\\myshare\ftp.mycompany.net\users\me"
##Copy file from one location to another
 $mytarget="C:\temp\test"
 Copy-Item $mypath $mytarget -recurse 
Get-ChildItem -Path $mytarget 

Get-ChildItem -Path $mypath  -Filter *.pdf -Recurse  -File -EA 0 | Select DirectoryName, Name,LastWriteTime,Length | convertto-csv -NoTypeInformation | % { $_ -replace '"', ''} | out-file $myexport | out-null
### bulk insert csv file to a table
AutoImportCommaFlatFiles -location "c:\temp\" -file "mypdffiles" -extension ".csv" -server "MC\MSSQL2014" -database "mydb1" | out-null



 

  
 
  CREATE PROCEDURE stp_CommaBulkInsert
@file NVARCHAR(250), @table NVARCHAR(250)
AS
BEGIN
 DECLARE @f NVARCHAR(250), @t NVARCHAR(250), @s NVARCHAR(MAX)
 SET @f = @file
 SET @t = @table
 
 SET @s = N'BULK INSERT ' + @t + '
  FROM ''' + @f + '''
  WITH (
   FIELDTERMINATOR = '',''
   ,ROWTERMINATOR = ''0x0a''
   ,FIRSTROW=2
  )'
 
 EXEC sp_executesql @s
END
 
--Check data from the target table
Select * from mypdffiles


 

Code from https://www.mssqltips.com/sqlservertip/3208/automating-flat-file-sql-server-imports-with-powershell/

Format Date Column in Excel From SQL Server Export File

When I export data to an Excel file from SSMS, the datetime column becomes a text format yyyy-mm-dd 00:00:00, for example: “2016-06-30 00:00:00”.
When you highlight the column to format the cell to date, it will not work.

A work around solution is:
Step 1: Highlight the column in question and Click on Data Tab>>Text to Columns >>with Delimited selected (default) click Next>> keep default Delimiters (Tab) >> click Next >> Column data format — choose Date in the format YMD and click Finish. The datetime values in the column become m/d/yyyy 0.00 format in excel now, for example: “6/30/2016 0.00”.

At this point, you can use Excel format cells function to format the data to the format you want. Highlight the column>> right click>> Format cells>> under Number tab — choose > Date … Choose from the Type: list. I choose m/dd/yyyy for my date column for now. >> click OK and save your excel file.

Dense_Rank with Data Island

  
 
  
 CREATE TABLE test (StartOfWeek DATETIME,Amount INT)
INSERT  INTO test(StartOfWeek, Amount) VALUES  ('1/17/2016',8),('1/24/2016',8),('1/31/2016',10),('2/7/2016',10),('2/14/2016',14),('2/21/2016',10),('2/28/2016',10)

;with mycte as (
select StartOfWeek, Amount  
, ROW_NUMBER()OVER (ORDER BY StartOfWeek) - ROW_NUMBER()OVER (Partition by Amount ORDER BY StartOfWeek) grp from test)

,mycte2 as (
Select * 
 ,row_number() Over(Order by StartOfWeek) - row_number() Over(Partition by grp Order by StartOfWeek) grp2 FROM mycte
)
SELECT StartOfWeek, Amount , DENSE_RANK() OVER(ORDER BY grp2) AS Ranking  FROM  mycte2
 
 

 drop table test

/*
2016-01-17 00:00:00.000	8	1
2016-01-24 00:00:00.000	8	1
2016-01-31 00:00:00.000	10	2
2016-02-07 00:00:00.000	10	2
2016-02-14 00:00:00.000	14	3
2016-02-21 00:00:00.000	10	4
2016-02-28 00:00:00.000	10	4

*/
-----https://social.msdn.microsoft.com/Forums/en-US/8c24f1a9-f267-4627-969c-5a30d78846a4/row-number?forum=transactsql