Archive
SQL Server – How to Move Table to Another Schema
Starting with SQL Server 2005 all tables are grouped into schemas. While creating a table if the schema name is not specified it is created in the default schema of the user creating it. you can use ALTER SCHEMA command to move tables between schemas.
For example, if I create a table using below script it will be created under my default schema which is dbo:
USE [SqlAndMe]
GO
CREATE TABLE Employee
(
ID INT,
Name VARCHAR(20)
)
GO
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM sys.tables
WHERE name = 'Employee'
GO
Result Set:
name schema
Employee dbo
(1 row(s) affected)
As you can see from the output the table is currently in dbo schema.
Now to move this table to another schema using ALTER SCHEMA command, first we need to create the schema if it does not exist already. After that we can move table to new schema.
USE [SqlAndMe]
GO
CREATE SCHEMA HumanResources
GO
ALTER SCHEMA HumanResources
TRANSFER dbo.Employee
GO
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM sys.tables
WHERE name = 'Employee'
GO
Result Set:
name schema
Employee HumanResources
(1 row(s) affected)
As you can see from the output the Employee table is now moved to HumanResources schema.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Import Data from Excel using T-SQL
To import data from an Excel file to SQL Server you can use SQL Server Import and Export Wizard. You can also import Excel data using T-SQL OPENROWSET function. OPENROWSET function can be used to import data from and OLEDB data source.
For this example I have created a simple excel file which has a single row of data and column headers, and the sheet is named [Employees] as below:
I have stored this file in my C:\ root folder.
Now to import this data to SQL Server you can use OPENROWSET as below:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\Emp.xlsx',
'SELECT * FROM [Employees$]')
Result Set:
As you can see this is very easy and straight forward. However, the above query only displays data from Excel file, it does not import it to database.
To import this data to SQL Server Table you can modify the SELECT statement to SELECT INTO
USE [SqlAndMe]
GO
SELECT * INTO dbo.ImportedEmployeeData
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\Emp.xlsx',
'SELECT * FROM [Employees$]')
GO
The above statement will create a new table ImportedEmployeeData and import excel data to the table.
Although this method seems straight forward, there are a few errors you may encounter:
Error Message 1:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error.
The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Cause 1: You may receive this error if the Excel file is currently opened.
Solution 1: Close the Excel file if it is currently opened and try executing again.
Error Message 2:
Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.
Cause 2: The cause of this error is mentioned in the error message. You may receive this error message if Ad Hoc Distributed Queries are not enabled by system administrator.
Solution 2: You can use sp_configure to enable this component. Since this is an advanced option you also need to enable 'Show Advanced Options' to enable this component. Use below commands to enable this:
EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
Result Set:
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
Error Message 3:
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Cause 3: You may receive this error message if registry settings are not set properly
Solution 3: To resolve this error, run below commands to fix registry issues:
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Saving Changes Not Permitted in Management Studio
SQL Server Management Studio does not allow you to save changes to a table which require table re-creation such as changing data type for a column. When you perform such changes you will run into following error message:
Here, I have tried changing data type for 'Name' column from NVARCHAR(50) to CHAR(50). Since this requires table re-creation Management Studio does not allow this operation to be completed, and it is doing so for your own good!
This activity can be completed without re-creating table if you are using T-SQL. Following T-SQL code can be used to avoid table re-creation:
USE [SqlAndMe]
GO
ALTER TABLE [dbo].[Customers]
ALTER COLUMN Name CHAR(50)
GO
The above statement will change the data type of the 'Name' column to CHAR(50) without having to re-create table.
We are better off with this option enabled. Although, if you are a GUI fan you can disable this safety net.
1. Go to Tools > Options
2. Go to Designers > Table and Database Designers
3. Uncheck Prevent saving changes that require table re-creation.
You can find more information on why not to disable this option here:
SQL Server – ALTER COLUMN – Management Studio v. T-SQL
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – How to Rename Table/Column in SQL Server
To rename an object in SQL Server you can use sp_rename system stored procedure or you can do this via Object Explorer in SQL Server Management Studio.
Method 1: To rename an object using SQL Server Management Studio:
Step 1. Right Click on the object in Object Explorer and select "Rename"
Step 2. Specify new name and press Enter.
You can rename any object using object in Object Explorer.
Method 2: Renaming an object using sp_rename:
You can also rename using sp_rename system stored procedure.
sp_rename takes below arguments:
| Parameter | Description |
| @objname | Object Name. When renaming a column you need to specify table name.column name optionally you can also prefix schema name |
| @newname | New name for the specified object |
| @objtype | Type of the object. You can rename below objects using sp_rename: COLUMN DATABASE INDEX OBJECT STATISTICS USERDATATYPE Default value for this parameter is TABLE |
Following example demonstrates how you can use sp_rename to rename table and column names:
Let’s create a Test Table to work with:
USE [SqlAndMe]
GO
CREATE TABLE tblProduct
(
ID INT,
pName NVARCHAR(50)
)
GO
Now we can rename the Table Name and column names as below:
– Rename table tblProduct to Table_Products
EXEC sp_rename 'tblProduct', 'Table_Products'
GO
– Rename Column ID to ProductID
EXEC sp_rename 'Table_Products.ID', 'ProductID', 'COLUMN'
GO
– Rename Column pName to ProductName
EXEC sp_rename 'Table_Products.pName', 'ProductName', 'COLUMN'
GO
Result Set:
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
You can verify that the changes have been made by issuing a SELECT against the table using new table name. SQL Server is also nice enough to warn us that renaming an object can break scripts and stored procedures. What this means is if you have used table/column names in any script/stored procedure it will not be updated automatically, you will need to go through your scripts/stored procedures manually and update them accordingly.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – DDL vs. DML Triggers
Although, DML and DDL triggers are used for different purposes, there are some similarities and differences between these.
Similarities between DDL and DML triggers:
1. Both are created using similar T-SQL syntax
2. Both can run .NET code
3. You can create multiple DDL and/or DML triggers on an object
4. The trigger and the triggering statement run as part of the same transaction
5. Both type of triggers can be nested
Differences between DDL and DML triggers:
1. There are no INSTEAD OF DDL triggers, they are only executed after the triggering statement is completed.
2. DML triggers use virtual tables INSERTED and DELETED which captures data modification, DDL triggers do not have this
3. DDL triggers captures information about the event that fired them using EVENTDATA() function.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Importing data from Excel – using INSERT statements
You can easily import data from an Excel file to SQL Server using SQL Server Import and Export Wizard. However, when the data is simple and limited I avoid using it (too lazy to click through 6 screens… 
) instead I generate INSERT statements using CONCATENATE function in Excel and execute those. This is much faster than using Import/Export Wizard.
This approach also requires the destination table to be created manually or it should already exists. Here’s how I use it:
1. The destination table I am using already exists with below columns, and some data:
2. The source Excel file contains below data:
3. Now to convert this to INSERT statement, we need to add a few columns to Excel sheet which will contain these texts "INSERT INTO dbo.ProductList VALUES (' ", " '' "," ' " etc. as shown below:
Note: if single quote (') is the first character in the column, as in Column C; you will need to input two single quotes (''). Also, if table has additional columns which are not being imported then you will need to include column list with INSERT statement i.e. "INSERT INTO dbo.ProductList (Name) VALUES (' " if you are importing only Name column.
4. Then you need to concatenate these columns using Excel's CONCATENATE function:
5. Finally, copy the same formula to all rows, these are the INSERT statements you need!:
6. That's all folks, Now copy them to SSMS and execute them…
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Exporting data to CSV file using SQLCMD/BCP
When it comes to exporting data there are different options available in SQL Server, such as SQL Server Integration Services, Generating Insert Scripts, BCP, Import and Export Wizard and SQLCMD.
When it comes to exporting data as CSV file, BCP and SQLCMD are the easiest methods. Both are command-line tools which can be used to export data.
General syntax for SQLCMD is as follows:
SQLCMD -S <<ServerName>> -E -Q "EXEC AdventureWorks2008R2.dbo.uspExport"
-s "," -o "C:\DataSqlCmd.csv" -h-1
Where, -S specifies Database Server Name,
-E specifies to use Windows Authentication,
-Q specifies the query to be executed against server,
-s specifies column separator character,
-o specifies the destination file and,
-h-1 specifies that column headers should not be exported.
You can provide a SELECT statement or a Stored Procedure to -Q switch. In general I use stored procedures as it allows to do any formatting required which cannot be done using SQLCMD.
I have created the stored procedure dbo.uspExport as below:
USE AdventureWorks2008R2
CREATE PROCEDURE uspExport
AS
SET NOCOUNT ON
SELECT TOP 5 BusinessEntityID,
JobTitle,
Gender,
BirthDate
FROM HumanResources.Employee
The stored procedure simply returns top5 rows which are to be exported.
Executing the above SQLCMD command will generate the CSV file as follows:
1,Chief Executive Officer ,M, 1963-03-02
2,Vice President of Engineering ,F, 1965-09-01
3,Engineering Manager ,M, 1968-12-13
4,Senior Tool Designer ,M, 1969-01-23
5,Design Engineer ,F, 1946-10-29
Same data can be exported using BCP as follows:
bcp "EXEC AdventureWorks2008R2.dbo.uspExport" queryout "C:\DataBCP.csv" -c -t , -S (local) -T
Here, -c specifies the character type format,
-t specifies the column separator,
-S specifies the database server name and,
-T specifies to use Windows Authentication.
The output generated by BCP is slightly different than of SQLCMD, output generated by BCP is as follows:
1,Chief Executive Officer,M,1963-03-02
2,Vice President of Engineering,F,1965-09-01
3,Engineering Manager,M,1968-12-13
4,Senior Tool Designer,M,1969-01-23
5,Design Engineer,F,1946-10-29
You can see that there is no padding while exporting using BCP by default, same can be achieved by using -W switch with SQLCMD.
If you need to implement any "complex" formatting, such as using delimiter for starting and end of row or change data format you can do this inside the stored procedure created for exporting data.
USE AdventureWorks2008R2
CREATE PROCEDURE uspExport
AS
SET NOCOUNT ON
SELECT TOP 5 '#' + CAST(BusinessEntityID AS NVARCHAR(MAX)) + '#' +
CAST(JobTitle AS NVARCHAR(MAX)) + '#' +
CAST(Gender AS NVARCHAR(MAX)) + '#' +
CONVERT(NVARCHAR(20), BirthDate, 107) + '#'
FROM HumanResources.Employee
Here I have returned result as a single column with specifying ‘#’ as a delimiter and to denote start/end of a row. So all rows returned by stored procedure will be pre-formatted and -s or -t switches of SQLCMD/BCP will have no effect.
#1#Chief Executive Officer#M#Mar 02, 1963#
#2#Vice President of Engineering#F#Sep 01, 1965#
#3#Engineering Manager#M#Dec 13, 1968#
#4#Senior Tool Designer#M#Jan 23, 1969#
#5#Design Engineer#F#Oct 29, 1946#
To see all switches available for BCP/SQLCMD refer BOL links or execute them with -? at command prompt.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
