bulk inset in sql server

Create CSV file in drive C: with name sweetest. text with the following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO

Find Stored Procedure name based on table name in sql server

Find Stored Procedure name based on table name in SQL SERVER


There are few query copy and paste in sql editor and change the name of table :


----Option 1 SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

Sql Offset

SELECT *
  FROM [fortune].[dbo].[TblStates] order by StateID desc Offset
  2 Rows fetch Next 2 Rows only;

This is important when the data is large and we need few record on click .

Note : offset work only in sql server 2012 and above version on change of offset number the result will change .

WITH ShowMessage(STATEMENT, LENGTH)AS
(
SELECT STATEMENT = CAST('I Am ' AS VARCHAR(300)), LEN('I Am') UNION ALL SELECT
CAST(STATEMENT + 'Sandeep! ' AS VARCHAR(300)) , LEN(STATEMENT)+ 9 FROM ShowMessage WHERE LENGTH < 300
)
SELECT STATEMENT, LENGTH FROM ShowMessage

Above we are keeping the record in tempoarary table that is showmessage  and in the last query we are fetching record from temp table.

read excel file in sql server

Here the code to read excel file from sql server

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\sheet(3-12-2013).xls','SELECT *  FROM [Sheet1$]')

Modify database path

but it gives while excuting query to overcome the problem Excute below syntex:

sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

and then

sp_configure 'show advanced options', 1;
RECONFIGURE;

Now the above query will work..

Comma seprated value in multiple rows sql server

Above is my output after executing the query but i want to to separated  row means multiple row splitting the taskid column by ' , '.

After so many searches and some modification i able to create a query which is very helpful for me -


SELECT  
     Split.a.value('.', 'VARCHAR(100)') AS taskid,  A.invoiceid,A.Rate
 FROM  (SELECT 
         CAST ('<M>' + REPLACE([taskids], ',', '</M><M>') + '</M>' AS XML) AS String , [invoiceid],[Rate]
     FROM  SMOInvoiceHistory) AS A CROSS APPLY String.nodes ('/M') AS Split(a)

Happy Coding...........