Move Database locations with detach/attach

USE MASTER;

GO

-- Take database in single user mode -- if you are facing errors

-- This may terminate your active transactions for database

ALTER DATABASE TestDB

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

GO

-- Detach DB

EXEC MASTER.dbo.sp_detach_db @dbname = N'TestDB'

GO

/*

--

-- Move MDF File from Loc1 to Loc2

--

*/

-- Re-Attached DB

CREATE DATABASE [TestDB] ON

( FILENAME = N'F:\loc2\TestDB.mdf' ),

( FILENAME = N'G:\loc2\TestDB_log.ldf' )

FOR ATTACH

GO

/* -------------------------- */

Move the TEMP Db's from one place to another

USE master

GO

ALTER DATABASE tempdb

        MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb2005.mdf')

GO

ALTER DATABASE tempdb

        MODIFY FILE (NAME = templog, FILENAME = 'C:\tempdb2005.ldf')

GO

/* -------------------------- */

Move your databases without a Detach, but a backup/restore methodology

/* 

--

--Quote: Backups are very easy, and protect you from all of the above [in link below].

*/

/*

BACKUP DATABASE [TestDB] TO DISK = 'x:\backups\TestDB.bak'

  WITH COPY_ONLY, INIT, COMPRESSION;

*/

/*

--

--Restoring is similarly easy, though if the file structures aren't the same,

--you may need to use the WITH MOVE arguments. This effort is worth it.

--

*/

/*

RESTORE TestDB dbname FROM DISK = 'x:\backups\TestDB.bak'

  WITH MOVE 'TestDB_Data' TO 'D:\MSSQL\Data\TestDB.mdf',

   MOVE 'TestDB_Log'  TO 'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\TestDB.ldf';

--

*/

--https://dba.stackexchange.com/questions/61810/what-are-the-risks-of-copying-mdf-ldf-files-without-detaching-the-db-first

Create Delilmiated Strings with COALESCE

DECLARE @EmployeeList varchar(100)


SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 

   CAST(Emp_UniqueID AS varchar(5))

FROM SalesCallsEmployees

WHERE SalCal_UniqueID = 1


SELECT @EmployeeList


https://www.sqlteam.com/articles/using-coalesce-to-build-comma-delimited-string


CreateTable from CSV

USE [CrouseScripts]

GO

/****** Object:  StoredProcedure [dbo].[createtable]    Script Date: 4/10/2020 9:32:20 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

BULK INSERT tablename

FROM 'n:\employee.csv' --This is CSV file

WITH ( FIELDTERMINATOR ='|',rowterminator = '\n',FIRSTROW = 1 )

*/

--select * from tablename

--drop table tablename

ALTER proc [dbo].[createtable]

@path nvarchar(2000),@outTableName nvarchar(2000)

as

--https://stackoverflow.com/questions/4344093/creating-table-with-the-same-columns-as-in-a-csv

begin

SET QUOTED_IDENTIFIER ON

declare @execSQL nvarchar(4000)

declare @tempstr varchar(4000)

declare @col varchar(4000)

declare @table nvarchar(4000)

-- Create a temp table to with one column to hold the first row of the csv file

  CREATE TABLE #tbl (line VARCHAR(8000))

   SET @execSQL =

        'BULK INSERT #tbl 

        FROM ''' + @path + ''' 

        WITH ( 

                 FIELDTERMINATOR =''|'',

                 FIRSTROW = 1, 

                 ROWTERMINATOR = ''\n'',

                 LASTROW = 1

              )        

       '

   EXEC sp_executesql @stmt=@execSQL

   select * from #tbl

   SET @col = ''

   SET @tempstr = (SELECT TOP 1 RTRIM(REPLACE(Line, CHAR(9), '|')) FROM #tbl)

   DROP TABLE #tbl

   WHILE CHARINDEX('|',@tempstr) > 0

    BEGIN          

       SET @col=@col + LTRIM(RTRIM(SUBSTRING(@tempstr, 1, CHARINDEX('|',@tempstr)-1))) + ' varchar(100),'    

          PRINT @col

       SET @tempstr = SUBSTRING(@tempstr, CHARINDEX('|',@tempstr)+1, len(@tempstr))

    END

       --PRINT @col

       if LEN(@tempstr) > 1

     BEGIN

     SET @col = @col + @tempstr + ' varchar(100)'

       END

        PRINT @outTableName

   if object_id(@outTableName) is not null

   exec dbo.procdroptable @TableName=@outTableName

 

   SET @table = 'create table '+@outTableName + ' (' + @col + ')'

   PRINT @table

   EXEC sp_executesql @stmt=@table

  

-- Load data from csv

   SET @execSQL =

        'BULK INSERT '+ @outTableName +'

        FROM ''' + @path + ''' 

        WITH ( 

                 FIELDTERMINATOR =''|'',

                 FIRSTROW = 2, 

                 ROWTERMINATOR = ''\n''             

              )        

       ' 

   EXEC sp_executesql @stmt=@execSQL

end

/*

ALTER PROCEDURE dbo.procdroptable

    @TABLENAME SYSNAME

AS

--https://dba.stackexchange.com/questions/39627/how-to-drop-tables-using-a-variable-in-sql-server

BEGIN

    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX)

    SELECT @SQL = 'DROP TABLE dbo.' + QUOTENAME(@TABLENAME) + '';

    EXEC sp_executesql @SQL;

END

GO

*/

procdroptable - Proc drop table

USE [CrouseScripts]

GO

/****** Object:  StoredProcedure [dbo].[procdroptable]    Script Date: 4/20/2020 10:53:19 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[procdroptable]

    @TABLENAME SYSNAME

AS

--https://dba.stackexchange.com/questions/39627/how-to-drop-tables-using-a-variable-in-sql-server

BEGIN

    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX)

    SELECT @SQL = 'DROP TABLE ' + QUOTENAME(@TABLENAME) + '';

    EXEC sp_executesql @SQL;

END



Cycle through a Formatted List (ie ",")

declare @S varchar(MAX)

set @S = '1,2,3,4,5'

while len(@S) > 0

begin
  --print left(@S, charindex(',', @S+',')-1)
  exec YourSP left(@S, charindex(',', @S+',')-1)
  set @S = stuff(@S, 1, charindex(',', @S+','), '')

end

https://stackoverflow.com/questions/10031658/comma-separated-list-in-sql