In few business applications inputs to the system is in the form of an TXT / CSV / Excel file(s).
This script will help how to upload an Excel file in to SQL Server.
Exporting from Excel to SQL
--------------
Documentation
-------------
Assumptions
1.Excel sheet should be saved in one of the local drives of SQL Server Box.
a. Excel sheet must have name “Sheet1” as tab defined, it cannot be renamed.
2.SQL table to which data needs to be exported must already exist in the database
3.SQL Table should have Primary Key defined.
4.The first row of excel sheet should be same as column names of SQL table.
The order of the columns in Excel needs to be same as in SQL table, and the names should match exactly.
Column names do not support spaces, for example “User name” is invalid.
1.If a column in excel has both integer and character type data, please ensure integer type data is saved as character type data in that column.
1.Uploading data from Excel sheet to SQL will be considered as “Incremental” instead of “Destructive” rebuild.
•If the data already exists in SQL, the data gets over-written from Excel table.
•New data in Excel will be inserted into SQL table.
•Data from SQL table will not be deleted; it needs to be done manually.
•Comparison between SQL table and Excel table is based of Primary Key.
2.SQL Server table with columns of following data type is not supported.
•ntext
•sql_variant
•text
•binary
•image
•varbinary
Steps to export data
1.Create couple of tables (say “XXX”, “YYY”. Any name should be okay)
2.Create a PK on these tables
3.Save Excel sheet in local drives of SQL Server Box
4.Please ensure Excel file is closed.
5.Using “Query Analyzer” execute the stored procedure.
--------------------------
-- Stored Procedure Script
--------------------------
IF Exists ( Select * From SysObjects
Where Type = 'p'
And Name = 'SchedImport_Excel2SQL')
Begin
PRINT 'Dropping Procedure SchedImport_Excel2SQL'
Drop Proc SchedImport_Excel2SQL
IF (@@Error <> 0)
BEGIN
RAISERROR ('Drop Procedure SchedImport_Excel2SQL Failed....',18,127)
END
End
GO
PRINT 'Creating Procedure SchedImport_Excel2SQL '
GO
-----------------------------------------------------------------------------
-- drop table x
-- create table x ( c1 int, c2 int, c3 int )
-- select * from x
-- EXEC SchedImport_Excel2SQL @ExcelFilename = 'd:\11.xls', @TableName = 'x'
-- EXEC SchedImport_Excel2SQL @ExcelFilename = 'd:\1.xls', @TableName = 'x'
-----------------------------------------------------------------------------
SET Quoted_Identifier OFF
GO
Create Procedure SchedImport_Excel2SQL
@ExcelFilename Varchar(8000),
@TableName Varchar(8000)
AS
------------------------------------------------------------------------------------------
-- Changed By Date Comments
------------------------------------------------------------------------------------------
-- pprabhu 01/07/2003 Created.
-- pprabhu 01/10/2003 Changed to handle Excel columnname AS Non-Alphabetical order
------------------------------------------------------------------------------------------
Begin
-------------------------
SET NOCOUNT ON
SET ARITHABORT OFF
SET Quoted_Identifier OFF
--SET ANSI_WARNINGS OFF
-------------------------
--------------------
-- Declare Variables
--------------------
Declare @SQL Varchar(8000),
@rc int,
@ExcelColumnListing Varchar(8000),
@SQLTableColumnListing Varchar(8000),
@MinCount Int,
@MaxCount Int,
@SQL_PK_NonPK_ColumnListMinCtr Int,
@SQL_PK_NonPK_ColumnListMaxCtr Int,
@SQL_PK_ColumnList Varchar(8000),
@SQL_NonPK_ColumnList Varchar(8000),
@SQL_PK_NonPK_ColumnListName_Excel Varchar(8000),
@SQL_PK_NonPK_ColumnListName_SQL Varchar(8000),
@PK_NonPK Int
---------------------------------------------------
-- Step 2.1 These variables are used in this STEP
---------------------------------------------------
,@DataFeedname_PK_Clus Int
,@DataFeedname_PK_NonClus Int
,@DataFeedModsName_PK_Clus Int
,@DataFeedModsName_PK_NonClus Int
----------------------------------
-- Initialize / Clean-up Variables
----------------------------------
Select @ExcelFilename = ISNULL(LTRIM(RTRIM(@ExcelFilename)),'')
,@TableName = ISNULL(LTRIM(RTRIM(@TableName)),'')
,@ExcelColumnListing = ''
,@SQLTableColumnListing = ''
----------------------------
-- Initial Level Validations
----------------------------
IF @ExcelFilename = ''
Begin
RAISERROR ('SchedImport_Excel2SQL : Input EXCEL File Not Passed',18,127)
RETURN -1
End
IF @TableName = ''
Begin
RAISERROR ('SchedImport_Excel2SQL : Input TableName Not Passed',18,127)
RETURN -1
End
IF NOT Exists ( Select * From SysObjects
Where Type = 'u'
And name = @TableName
)
Begin
RAISERROR ('SchedImport_Excel2SQL : Input TableName Does not Exists in this database',18,127)
RETURN -1
End
--------------------------------------
-- Identify if the Input tables has PK
--------------------------------------
IF NOT Exists ( Select soc.name
From sysobjects soc, sysobjects sop
Where soc.id = sop.parent_obj
And sop.xtype = 'PK'
And soc.name = @TableName
)
Begin
RAISERROR ('SchedImport_Excel2SQL : - Input Table does not have a Primary Key',18,127)
RETURN -1
End
------------------------
-- Create Working tables
------------------------
IF EXISTS ( Select * From Sysobjects
Where Name = 'Working_getfiledetails'
And Type = 'u'
)
Begin
Drop Table Working_getfiledetails
End
Create Table Working_getfiledetails
(
[Alternate Name] Varchar(255)
,[Size] Int
,[Creation Date] Int
,[Creation Time] Int
,[Last Written Date] Int
,[Last Written Time] Int
,[Last Accessed Date] Int
,[Last Accessed Time] Int
,[Attributes] Int
)
IF EXISTS ( Select * From Sysobjects
Where Name = 'Working_Excel2SQLData'
And Type = 'u'
)
Begin
Drop table Working_Excel2SQLData
End
IF EXISTS ( Select * From Sysobjects
Where Name = 'Working_Excel2SQLData_AlphaBetized'
And Type = 'u'
)
Begin
Drop table Working_Excel2SQLData_AlphaBetized
End
--Select * From Sysobjects Where Name = 'Working_Excel2SQLData_AlphaBetized' And Type = 'u'
--------------------------
-- Populate Working tables
--------------------------
INSERT INTO Working_getfiledetails
EXEC master..xp_getfiledetails @ExcelFilename
/*
Exec @rc = master..xp_getfiledetails @ExcelFilename
IF @RC <> 0
Begin
RAISERROR ('SchedImport_Excel2SQL : Input EXCEL File Not Found',18,127)
RETURN -1
End
*/
IF NOT Exists ( Select * From Working_getfiledetails )
Begin
RAISERROR ('SchedImport_Excel2SQL : Input EXCEL File Not Found',18,127)
RETURN -1
End
----------------------------------
-- Construct the SQL Statement
-- SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=d:\1.xls;Extended Properties=Excel 8.0')...[sheet1$]
----------------------------------
--Select @SQL = 'SET QUOTED_IDENTIFIER OFF ' + CHAR(10)
Select @SQL = 'Data Source=' + @ExcelFilename +';Extended Properties=Excel 8.0'
Select @SQL = 'SELECT * INTO ' + 'Working_Excel2SQLData_AlphaBetized'
+ ' FROM OPENDATASOURCE('
+ '''' + 'Microsoft.Jet.OLEDB.4.0' + '''' + ','
+ '''' + @SQL + ''''
+ ')...[sheet1$]'
--Select @SQL = '"SET QUOTED_IDENTIFIER OFF ' + char(10) + @SQL + '"'
--Select @SQL = 'SET QUOTED_IDENTIFIER OFF ' + CHAR(10) + @SQL
--Select @SQL
--return
exec (@SQL)
--SELECT * FROM Working_Excel2SQLData_AlphaBetized
--return
-----------------------------------------------------
-- If NO Data From Excel File, ignore that Excel File
-----------------------------------------------------
IF NOT Exists ( SELECT * FROM Working_Excel2SQLData_AlphaBetized )
Begin
RAISERROR ('SchedImport_Excel2SQL : No Input data in EXCEL File. Nothing to upload',18,127)
RETURN -1
End
----------------------------------------------
-- If COUNT of Columns in EXCEL Does not match
-- the COUNT in SQL, it should be rolled back
----------------------------------------------
IF ( Select Count(*)
From SysColumns
Where ID = Object_ID(@TableName)
) <>
( Select Count(*)
From SysColumns
Where ID = Object_ID('Working_Excel2SQLData_AlphaBetized')
)
Begin
RAISERROR ('SchedImport_Excel2SQL : Number of Columns in SQL Table vs Excel not matching',18,127)
RETURN -1
End
-------------------------------------------------------------------------------
-- If Names of Columns in EXCEL Does not match in SQL, it should be rolled back
-------------------------------------------------------------------------------
IF ( Select Count(*)
From SysColumns x, SysColumns s
Where s.ID = Object_ID(@TableName)
And x.ID = Object_ID('Working_Excel2SQLData_AlphaBetized')
--And s.ColID = x.ColID
And s.name = x.name
) <>
(
Select Count(*)
From SysColumns
Where ID = Object_ID(@TableName)
)
Begin
RAISERROR ('SchedImport_Excel2SQL : Columns Names in SQL Table vs Excel not matching',18,127)
RETURN -1
End
--------------------------------------------------------------------------------------
-- Move the data from [Working_Excel2SQLData_AlphaBetized] Version to required version
--------------------------------------------------------------------------------------
Select @SQL = ''
-------------------------------------------------------------------
-- Identify the Column names of the Source Table in the COLID order
-------------------------------------------------------------------
SELECT @SQLTableColumnListing = ''
SELECT @SQLTableColumnListing = COALESCE(CONVERT(VARCHAR(255),@SQLTableColumnListing) + ',', "")
+ CONVERT(VARCHAR(255),Name)
FROM SysColumns
WHERE ID = Object_ID(@TableName)
Order by ColID
Select @SQLTableColumnListing = LTRIM(RTRIM(@SQLTableColumnListing))
IF SubString(@SQLTableColumnListing,1,1) = ','
Begin
SELECT @SQLTableColumnListing = SubString(@SQLTableColumnListing,2,datalength(@SQLTableColumnListing)-1)
End
--SELECT @SQLTableColumnListing
Select @SQL = 'SELECT ' + @SQLTableColumnListing + ' INTO '
+ 'Working_Excel2SQLData' + ' '
+ 'From Working_Excel2SQLData_AlphaBetized'
--Select @SQL
exec (@SQL)
--select * from Working_Excel2SQLData_AlphaBetized
--select * from Working_Excel2SQLData
--return
-------------------------------------------------
-- If DATATYPE of Columns in EXCEL Does not match
-- the DATATYPE in SQL, it should be rolled back
-------------------------------------------------
-- select xtype, name from master..systypes order by name
If Exists ( Select A.* From
(
Select 'ExcelColID' = x.ColID,
'ExcelName' = x.Name,
'ExcelxType' = x.xtype,
Case x.xtype
When 127 Then 56 -- BigInt to Int
When 104 Then 56 -- bit to Int
When 106 Then 56 -- decimal to Int
When 56 Then 56 -- Int to Int
When 62 Then 56 -- float to Int
When 60 Then 56 -- money to Int
When 108 Then 56 -- numeric to Int
When 59 Then 56 -- real to Int
When 52 Then 56 -- smallint to Int
When 122 Then 56 -- smallmoney to Int
When 48 Then 56 -- tinyint to Int
When 175 Then 175 -- char to Char
When 239 Then 175 -- nchar to Char
When 99 Then 175 -- ntext to Char
When 231 Then 175 -- nvarchar to Char
When 231 Then 175 -- sysname to Char
When 98 Then 175 -- sql_variant to Char
When 35 Then 175 -- text to Char
When 189 Then 175 -- timestamp to Char
When 36 Then 175 -- uniqueidentifier to Char
When 167 Then 175 -- varchar to Char
When 173 Then 175 -- binary to Char
When 34 Then 175 -- image to Char
When 165 Then 175 -- varbinary to Char
When 61 Then 61 -- datetime to datetime
When 58 Then 61 -- smalldatetime to datetime
Else 175 -- Unknown to Char
End AS ExcelColumnType,
'SQLColID' = s.ColID,
'SQLName' = s.Name,
'SQLxType' = s.xtype,
Case s.xtype
When 127 Then 56 -- BigInt to Int
When 104 Then 56 -- bit to Int
When 106 Then 56 -- decimal to Int
When 56 Then 56 -- Int to Int
When 62 Then 56 -- float to Int
When 60 Then 56 -- money to Int
When 108 Then 56 -- numeric to Int
When 59 Then 56 -- real to Int
When 52 Then 56 -- smallint to Int
When 122 Then 56 -- smallmoney to Int
When 48 Then 56 -- tinyint to Int
When 175 Then 175 -- char to Char
When 239 Then 175 -- nchar to Char
When 99 Then 175 -- ntext to Char
When 231 Then 175 -- nvarchar to Char
When 231 Then 175 -- sysname to Char
When 98 Then 175 -- sql_variant to Char
When 35 Then 175 -- text to Char
When 189 Then 175 -- timestamp to Char
When 36 Then 175 -- uniqueidentifier to Char
When 167 Then 175 -- varchar to Char
When 173 Then 175 -- binary to Char
When 34 Then 175 -- image to Char
When 165 Then 175 -- varbinary to Char
When 61 Then 61 -- datetime to datetime
When 58 Then 61 -- smalldatetime to datetime
Else 175 -- Unknown to Char
End AS SQLColumnType
From SysColumns x, SysColumns s
Where x.ID = Object_ID('Working_Excel2SQLData')
And s.ID = Object_ID(@TableName)
--And s.ID = Object_ID('x')
And x.ColID = s.ColID
) As A
Where A.ExcelColumnType <> A.SQLColumnType
)
Begin
RAISERROR ('SchedImport_Excel2SQL : Column datatype between Excel table & SQL table does not match',18,127)
RETURN -1
End
----------------------------------------------------------------------------------
-- If Data in 'Working_Excel2SQLData' is NULL, check if @TableName will support it
----------------------------------------------------------------------------------
-- select isnullable , * from syscolumns where id=Object_id('y') -- 1 - Null, 0 - not null
IF Exists ( Select s.ColId, s.Name,
x.ColId, x.Name
From SysColumns x, SysColumns s
Where x.ID = Object_ID('Working_Excel2SQLData')
And s.ID = Object_ID(@TableName)
--And s.ID = Object_ID('x')
And x.ColID = s.ColID
And s.isnullable = 0 -- 1 - Null, 0 - not null
)
Begin
Select @MinCount = ISNULL(MIN(ColID),0)
,@MaxCount = ISNULL(MAX(ColID),0)
From SysColumns
Where ID = Object_ID(@TableName)
--Where ID = Object_ID('x')
--Select @MinCount, @MaxCount
Select @SQL = ''
While @MinCount <= @MaxCount
Begin
IF Exists ( Select *
From SysColumns
Where ID = Object_ID(@TableName)
--Where ID = Object_ID('x')
And ColID = @MinCount
And isnullable = 0 -- 1 - Null, 0 - not null
)
Begin
IF @SQL = '' -- Reading the Very First Column
Begin
Select @SQL = @SQL + Name + ' IS NULL '
From SysColumns
Where ID = Object_ID('Working_Excel2SQLData')
And ColID = @MinCount
End
ELSE -- Reading the 2nd, 3rd, 4th.... Columns
Begin
Select @SQL = @SQL + ' OR ' + Name + ' IS NULL '
From SysColumns
Where ID = Object_ID('Working_Excel2SQLData')
And ColID = @MinCount
End
End
Select @MinCount = @MinCount + 1
End
Select @SQL = 'Select x = Count(*) From Working_Excel2SQLData '
+ 'Where 1=1 AND ( ' + @SQL + ' ) '
-----------------------------------------
-- Count if ANY Null values in EXCEL file
-----------------------------------------
IF Exists ( Select * from sysobjects
where name = 'ExcelTableCounts'
And Type = 'u'
)
Begin
Drop Table ExcelTableCounts
End
Create table ExcelTableCounts ( x int )
Insert INTO ExcelTableCounts (x)
EXEC (@SQL)
IF Exists ( Select * From ExcelTableCounts Where x > 0 )
Begin
RAISERROR ('SchedImport_Excel2SQL : Excel sheet has some NULL data, in some Column(s), which cannot be uploaded',18,127)
RETURN -1
End
IF Exists ( Select * from sysobjects
where name = 'ExcelTableCounts'
And Type = 'u'
)
Begin
Drop Table ExcelTableCounts
End
End
--------------------------------
-- Finally Insert into the Table
--------------------------------
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- 1. Identify the Columns of the Excel table
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT @ExcelColumnListing = ''
SELECT @ExcelColumnListing = COALESCE(CONVERT(VARCHAR(255),@ExcelColumnListing) + ',', "")
+ CONVERT(VARCHAR(255),Name)
FROM SysColumns
WHERE ID = Object_ID('Working_Excel2SQLData')
Order by ColID
Select @ExcelColumnListing = LTRIM(RTRIM(@ExcelColumnListing))
IF SubString(@ExcelColumnListing,1,1) = ','
Begin
SELECT @ExcelColumnListing = SubString(@ExcelColumnListing,2,datalength(@ExcelColumnListing)-1)
End
--SELECT @ExcelColumnListing
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- 2. Identify the Columns of the SQL table
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT @SQLTableColumnListing = ''
SELECT @SQLTableColumnListing = COALESCE(CONVERT(VARCHAR(255),@SQLTableColumnListing) + ',', "")
+ CONVERT(VARCHAR(255),Name)
FROM SysColumns
WHERE ID = Object_ID(@TableName)
Order by ColID
Select @SQLTableColumnListing = LTRIM(RTRIM(@SQLTableColumnListing))
IF SubString(@SQLTableColumnListing,1,1) = ','
Begin
SELECT @SQLTableColumnListing = SubString(@SQLTableColumnListing,2,datalength(@SQLTableColumnListing)-1)
End
--SELECT @SQLTableColumnListing
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- 3. If data already exists (based on PK column matching), replace them with new data
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Select @SQL = ''
-----------------------------------------------------------------
-- Step 1 : Drop / Create Work table to identify all the Columns
-----------------------------------------------------------------
If Exists ( Select * From Sysobjects
Where Name = 'WorkColumnListing_With_PKNonPKCol'
And Type = 'u'
)
Begin
Drop Table WorkColumnListing_With_PKNonPKCol
IF @@Error <> 0
Begin
RAISERROR ('SchedImport_Excel2SQL : - Drop Table WorkColumnListing_With_PKNonPKCol Failed',18,127)
RETURN -1
End
End
Create table WorkColumnListing_With_PKNonPKCol ( ColId INT, SQLName SysName, ExcelName SysName, PK_NonPK Int )
IF @@Error <> 0
Begin
RAISERROR ('SchedImport_Excel2SQL : - Create Table WorkColumnListing_With_PKNonPKCol Failed',18,127)
RETURN -1
End
--------------------------------
-- Step 2 : Populate Work table
--------------------------------
-- Select * from WorkColumnListing_With_PKNonPKCol
Insert INTO WorkColumnListing_With_PKNonPKCol ( ColId , SQLName, ExcelName, PK_NonPK )
Select s.ColId, s.Name, x.Name, PK_NonPK = 0
From SysColumns x, SysColumns s
Where x.ID = Object_ID('Working_Excel2SQLData')
And s.ID = Object_ID(@TableName)
--And s.ID = Object_ID('x')
And x.ColID = s.ColID
Order by s.ColID
IF @@Error <> 0
Begin
RAISERROR ('SchedImport_Excel2SQL : - Populate Table WorkColumnListing_With_PKNonPKCol Failed',18,127)
RETURN -1
End
---------------------------------------------------------------------------------------------------
-- Step 2.1 Identify the Primary Key names & its Type, if its Clustered on Non-Clustered - Starts
---------------------------------------------------------------------------------------------------
Exec @RC = SchedImport_DataFeedIndexType_Status
@DataFeedname = @TableName
,@DataFeedModsName = @TableName ----- Take Care of this value
,@DataFeedname_PK_Clus = @DataFeedname_PK_Clus OUTPUT
,@DataFeedname_PK_NonClus = @DataFeedname_PK_NonClus OUTPUT
,@DataFeedModsName_PK_Clus = @DataFeedModsName_PK_Clus OUTPUT
,@DataFeedModsName_PK_NonClus = @DataFeedModsName_PK_NonClus OUTPUT
IF @RC <> 0
Begin
RAISERROR ('SchedImport_Populate : Error executing SchedImport_DataFeedIndexType_Status',18,127)
RETURN -1
End
--Select @DataFeedname_PK_Clus, @DataFeedname_PK_NonClus, @DataFeedModsName_PK_Clus, @DataFeedModsName_PK_NonClus
--return
---------------------------------------------------------------------------------------------------
-- Step 2.1 Identify the Primary Key names & its Type, if its Clustered on Non-Clustered - Ends
---------------------------------------------------------------------------------------------------
---------------------------------------------------
-- Step 3 : Identify Which of these are PK Columns
---------------------------------------------------
Select @SQL_PK_NonPK_ColumnListMinCtr = ISNULL(MIN(ColId),0),
@SQL_PK_NonPK_ColumnListMaxCtr = ISNULL(MAX(ColId),0)
From WorkColumnListing_With_PKNonPKCol
While @SQL_PK_NonPK_ColumnListMinCtr <= @SQL_PK_NonPK_ColumnListMaxCtr
Begin
IF (INDEX_COL(@TableName, 1, @SQL_PK_NonPK_ColumnListMinCtr)) IS NOT Null OR
(INDEX_COL(@TableName, 2, @SQL_PK_NonPK_ColumnListMinCtr)) IS NOT Null
Begin
IF (INDEX_COL(@TableName, 1, @SQL_PK_NonPK_ColumnListMinCtr)) IS NOT Null
AND
@DataFeedName_PK_Clus = 1
Begin
Update WorkColumnListing_With_PKNonPKCol
Set PK_NonPK = 1 -- These are PK columns
--Where ColId = @SQL_PK_NonPK_ColumnListMinCtr
Where SQLName = INDEX_COL(@TableName, 1, @SQL_PK_NonPK_ColumnListMinCtr)
IF @@Error <> 0
Begin
RAISERROR ('SchedImport_Excel2SQL : - Update Table WorkColumnListing_With_PKNonPKCol Failed',18,127)
RETURN -1
End
End
IF (INDEX_COL(@TableName, 2, @SQL_PK_NonPK_ColumnListMinCtr)) IS NOT Null
AND
@DataFeedName_PK_NonClus = 1
Begin
Update WorkColumnListing_With_PKNonPKCol
Set PK_NonPK = 1 -- These are PK columns
--Where ColId = @SQL_PK_NonPK_ColumnListMinCtr
Where SQLName = INDEX_COL(@TableName, 2, @SQL_PK_NonPK_ColumnListMinCtr)
IF @@Error <> 0
Begin
RAISERROR ('SchedImport_Excel2SQL : - Update Table WorkColumnListing_With_PKNonPKCol Failed',18,127)
RETURN -1
End
End
End
Select @SQL_PK_NonPK_ColumnListMinCtr = @SQL_PK_NonPK_ColumnListMinCtr + 1
End
--select * from WorkColumnListing_With_PKNonPKCol
--return
-----------------------------------------------------------------------------
-- Step 4 : Rest of then Non-PK columns used for comparison of data EQUALITY
-- Generate SQL statement of NON-PK & PK Columns explicitly.
-----------------------------------------------------------------------------
Select @SQL_PK_ColumnList = ''
Select @SQL_NonPK_ColumnList = ''
Select @SQL_PK_NonPK_ColumnListMinCtr = ISNULL(MIN(ColId),0),
@SQL_PK_NonPK_ColumnListMaxCtr = ISNULL(MAX(ColId),0)
From WorkColumnListing_With_PKNonPKCol
While @SQL_PK_NonPK_ColumnListMinCtr <= @SQL_PK_NonPK_ColumnListMaxCtr
Begin
IF Exists ( Select * From WorkColumnListing_With_PKNonPKCol
Where ColId = @SQL_PK_NonPK_ColumnListMinCtr
)
Begin
Select @SQL_PK_NonPK_ColumnListName_Excel = ExcelName
,@SQL_PK_NonPK_ColumnListName_SQL = SQLName
,@PK_NonPK = PK_NonPK
From WorkColumnListing_With_PKNonPKCol
Where ColId = @SQL_PK_NonPK_ColumnListMinCtr
--Select @SQL_PK_NonPK_ColumnListName, @PK_NonPK
IF @PK_NonPK = 0 -- 0 : NonPk, generate "OR" clause SQL
Begin
IF @SQL_NonPK_ColumnList = '' -- Reading the Very First Column
Begin
Select @SQL_NonPK_ColumnList = @SQL_NonPK_ColumnList
+ 's.' + @SQL_PK_NonPK_ColumnListName_SQL + ' = ' + ' x.' + @SQL_PK_NonPK_ColumnListName_Excel + ' '
End
ELSE -- Reading the 2nd, 3rd, 4th.... Columns
Begin
Select @SQL_NonPK_ColumnList = @SQL_NonPK_ColumnList + ' , '
+ 's.' + @SQL_PK_NonPK_ColumnListName_SQL + ' = ' + ' x.' + @SQL_PK_NonPK_ColumnListName_Excel + ' '
End
End
ELSE --IF @PK_NonPK = 1 -- : PK, generate "JOIN Column" clause SQL
Begin
IF @SQL_PK_ColumnList = '' -- Reading the Very First Column
Begin
Select @SQL_PK_ColumnList = @SQL_PK_ColumnList
+ '( s.' + @SQL_PK_NonPK_ColumnListName_SQL + ' = ' + ' x.' + @SQL_PK_NonPK_ColumnListName_Excel + ' ) '
End
ELSE -- Reading the 2nd, 3rd, 4th.... Columns
Begin
Select @SQL_PK_ColumnList = + @SQL_PK_ColumnList + ' AND '
+ '( s.' + @SQL_PK_NonPK_ColumnListName_SQL + ' = ' + ' x.' + @SQL_PK_NonPK_ColumnListName_Excel + ' ) '
End
End
End
Select @SQL_PK_NonPK_ColumnListMinCtr = @SQL_PK_NonPK_ColumnListMinCtr + 1
End
--------------------------------------------------------------
-- Ensure that EXCEL file has no duplicates in the PK columns
--------------------------------------------------------------
Select @SQL = ''
SELECT @SQL = COALESCE(CONVERT(VARCHAR(255),@SQL) + ',', "")
+ CONVERT(VARCHAR(255),SQLName)
FROM WorkColumnListing_With_PKNonPKCol
WHERE PK_NonPK = 1
Select @SQL = LTRIM(RTRIM(@SQL))
IF SubString(@SQL,1,1) = ','
Begin
SELECT @SQL = SubString(@SQL,2,datalength(@SQL)-1)
End
--SELECT @SQL
/*
Select x = Count(*)
From Working_Excel2SQLData
Group By Title,Description
Having Count(*) > 1
*/
Select @SQL = ' Select x = Count(*) '
+ ' From Working_Excel2SQLData '
+ ' Group By ' + @SQL
+ ' Having Count(*) > 1 '
--SELECT @SQL
--return
----------------------------------------------------------------
-- Count if ANY Duplicate values in EXCEL file esp. in PK Column
----------------------------------------------------------------
IF Exists ( Select * from sysobjects
where name = 'ExcelTableCounts'
And Type = 'u'
)
Begin
Drop Table ExcelTableCounts
End
Create table ExcelTableCounts ( x int )
Insert INTO ExcelTableCounts (x)
EXEC (@SQL)
IF Exists ( Select * From ExcelTableCounts Where x > 0 )
Begin
RAISERROR ('SchedImport_Excel2SQL : Excel sheet has some Duplicate data, in PK columns',18,127)
RETURN -1
End
--SELECT * From ExcelTableCounts
--return
IF Exists ( Select * from sysobjects
where name = 'ExcelTableCounts'
And Type = 'u'
)
Begin
Drop Table ExcelTableCounts
End
------------------------------
-- Clean Column List Variable
------------------------------
Select @SQL_PK_ColumnList = LTRIM(RTRIM(ISNULL(@SQL_PK_ColumnList,'')))
Select @SQL_NonPK_ColumnList = LTRIM(RTRIM(ISNULL(@SQL_NonPK_ColumnList,'')))
--Select '@SQL_PK_ColumnList', @SQL_PK_ColumnList
--Select '@SQL_NonPK_ColumnList' , @SQL_NonPK_ColumnList
--return
-------------------------------
-- Update the Existing records
-------------------------------
/*
Update s
Set s.c2 = x.b,
s.c4 = x.d,
s.c5 = x.e
From x s, Working_Excel2SQLData x
Where 1=1
AND (
( s.c1 = x.a ) AND ( s.c3 = x.c )
)
*/ Select @SQL = ''
Select @SQL = 'Update s '
+ 'Set ' + @SQL_NonPK_ColumnList + ' '
+ 'From ' + @TableName + ' s, Working_Excel2SQLData x '
+ 'Where 1=1 AND '
+ '('
+ @SQL_PK_ColumnList
+ ')'
--Select @SQL
--return
Exec (@SQL)
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- 4. Delete the Updated records
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Select @SQL = ''
Select @SQL = 'Delete x '
+ 'From ' + @TableName + ' s, Working_Excel2SQLData x '
+ 'Where 1=1 AND '
+ '('
+ @SQL_PK_ColumnList
+ ')'
--Select @SQL
--return
Exec (@SQL)
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- 5. Comute INSERT Query (Only new data gets inserted)
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Select @SQL = ''
Select @SQL = ' Insert INTO ' + @TableName + '(' + @SQLTableColumnListing + ')'
+ ' Select ' + @ExcelColumnListing
+ ' From ' + ' Working_Excel2SQLData '
--Select @SQL
--return
Exec (@SQL)
------------------------------
-- Delete all un-wanted tables
------------------------------
IF EXISTS ( Select * From Sysobjects
Where Name = 'Working_getfiledetails'
And Type = 'u'
)
Begin
Drop Table Working_getfiledetails
End
IF EXISTS ( Select * From Sysobjects
Where Name = 'Working_Excel2SQLData'
And Type = 'u'
)
Begin
Drop table Working_Excel2SQLData
End
IF EXISTS ( Select * From Sysobjects
Where Name = 'Working_Excel2SQLData_AlphaBetized'
And Type = 'u'
)
Begin
Drop table Working_Excel2SQLData_AlphaBetized
End
If Exists ( Select * From Sysobjects
Where Name = 'WorkColumnListing_With_PKNonPKCol'
And Type = 'u'
)
Begin
Drop Table WorkColumnListing_With_PKNonPKCol
IF @@Error <> 0
Begin
RAISERROR ('SchedImport_Excel2SQL : - Drop Table WorkColumnListing_With_PKNonPKCol Failed',18,127)
RETURN -1
End
End
End
GO
IF @@error = 0
Begin
Print 'Created Procedure SchedImport_Excel2SQL Sucessfully !!!'
End
ELSE
Begin
RaisError ( 'Procedure SchedImport_Excel2SQL Creation Failed ...', 18,127)
End
GO