Import CSV using “Microsoft Access Database Engine 2010” to SQL Server


sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure with override

SET NOCOUNT ON

EXEC master..xp_cmdshell 'del C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo [Test.txt] > C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo ColNameHeader=True >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Format=Delimited(^|) >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo MaxScanRows=50 >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo CharacterSet=OEM >> C:\Temp\Schema.ini'

EXEC master..xp_cmdshell 'echo Col1=ID TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col2=LastName TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col3=FirstName TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col4=MiddleName TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col5=MailingAddressLine1 TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col6=MailingAddressline2 TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col7=MailingAddressCity TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col8=MailingAddressState TEXT >> C:\Temp\Schema.ini'
EXEC master..xp_cmdshell 'echo Col9=MailingAddressZIP TEXT >> C:\Temp\Schema.ini'

EXEC ('INSERT INTO ImportTest (ID,LastName, FirstName,MiddleName, MailingAddressLine1,MailingAddressline2,MailingAddressCity, MailingAddressState,MailingAddressZIP)
SELECT ID,LastName, FirstName,MiddleName, MailingAddressLine1,MailingAddressline2,MailingAddressCity, MailingAddressState,MailingAddressZIP
FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Text;Database=C:\Temp\;HDR=YES'',''select * from person.txt'')
')
EXEC master..xp_cmdshell 'del C:\Temp\Schema.ini'

How to Backup Microsoft SQL Server (MSSQL) Databases

--1. Variable declaration

DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @filename VARCHAR(256)
 
-- 2. Setting the backup path

SET @path = 'X:\MSSQLDB\Backup\'  

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  -- ignore system databases

--3. Initializing cursor operations

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN

  -- 4. Defining the filename
    SET @fileName = @path + @name + '.BAK'  
    BACKUP DATABASE @name TO DISK = @fileName 

    FETCH NEXT FROM db_cursor INTO @name   
END   
CLOSE db_cursor   
DEALLOCATE db_cursor