How to import CSV / Excel file to SQL Server Table

How to Load Excel File / CSV File to a SQL Server Table / Create SQL Server Table from Excel File

Step 1: Select Import and Export Wizard from Program


Step 2: Select Import and Export Wizard from Program

 Step 3: Select Flat File as Source

Step 4: Select the CSV / Text file

Step 5: Choose the Server

Step 6: Choose Database

Step 7: Specify Schema and Table Names

 Step 8: Run the Pachage

 

Specified column precision 50 is greater than the maximum precision of 38.

What is the Preferred Data Type for Price and Amount Fields

This error occurs when you are trying to define a Decimal field with precision more than 38. If you would require more precission, please use any of the following:

[Sales Price] [Numeric](50,2) NULL,

[Quantity] [int](50) NULL,

[Amount] [Numeric](50,2) NULL,

Money
Float



CREATE TABLE [dbo].[Transact.Details](
[Store ID] [varchar](50) NULL,
[Customer ID] [varchar](50) NULL,
[Transaction ID] [varchar](50) NULL,
[Transaction Date] [varchar](50) NULL,
[Category ID] [varchar](50) NULL,
[Item ID] [varchar](50) NULL,
[Sales Price] [Numeric](38,2) NULL,
[Quantity] [int] NULL,
[Amount] [Numeric](38,2) NULL,
) ON [PRIMARY]


SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

The error occurs when I try to run the BCP utility

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

How to Export Query as XML - SQL Server 2008

Save Recordset as XML using Select Query in SQL Server 2008

Let us consider the a table (shown below) with couple of columns that needs to be exported as XML

The following query

SELECT TOP 1000 [TrainID] as 'TrainNum'

,[TrainName]

FROM [OnlineTrans].[dbo].[TrainMaster] for XML PATH

Is used for generating a XML 

 On the other hand, If you want to have a meaningfully named element instead of , you need to specify the same in the query. The following query has the row element as well as the root element


SELECT TOP 1000 [TrainID] as 'TrainNum'

,[TrainName]

FROM [OnlineTrans].[dbo].[TrainMaster] for XML path ('TrainInfo'), root ('ParentInfo')

'int' is not a recognized CURSOR option.

This error occurs when a variable name is preceded by # instead of @. # is used to declare temporary table

declare #myi int

Should be replaced by

declare @myi int

While Loop in SQL Server

While loop in T-SQL

Here is a simple example of While loop

Declare @temp tinyint = 1

while (@temp < 11)

begin

print cast(@temp as varchar(2)) + ' X 2 = ' + cast(@temp * 2 as char)

set @temp += 1

end

The above code will be having the following output:

Output

1 X 2 = 2

2 X 2 = 4

3 X 2 = 6

4 X 2 = 8

5 X 2 = 10

6 X 2 = 12

7 X 2 = 14

8 X 2 = 16

9 X 2 = 18

10 X 2 = 20

How to create a SQL Function that Returns a Table

SQL Create Table Valued Function (SQL SERVER 2008) / How to Create SQL_TABLE_VALUED_FUNCTION

The following snippet uses a Table variable to create a table and inserts value into it, which the function returns

CREATE FUNCTION ReturnATable()
RETURNS @TabVar TABLE
(
 OrderID int not null,
 OrderDate datetime,
 OrderStatus bit,
 OrderValue decimal,
 BilledBy varchar(20)
)
AS
BEGIN
DEclare @Date datetime ;
Set @Date= GETDATE();
Insert Into @TabVar values
(21, @Date , 1, 212.42, 'Jose');
return 
END

The function can be executed as shown below

Select * from ReturnATable()