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
/* -------------------------- */
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
/* -------------------------- */
/*
--
--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
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
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
*/
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