Update SQL Table with joins

Following is the query to update sql table with joins

UPDATE a

SET a.sortorder = b.sort_order

FROM picture AS a

INNER JOIN (select row_number() over (order by id) as sort_order,id,projectid from picture

where projectid = 61

) AS b

       ON a.id = b.id

where a.projectid = 61

select * from picture order by id desc

SQL REPLACE() : Argument data type ntext is invalid for argument 1 of replace function.

SQL Server Replace function does not work with text and ntext data types.

Following query contains replace function for ntext type column.

update description_table set description = replace(description,'../G/','http://styletint.in/G/') where description like '%../G/%'

this query is will return following error:

Argument data type ntext is invalid for argument 1 of replace function.

To resolve this, we can cast ntext type to nvarchar(max) and then update column with this value: Following query will work without any error:

update description_table set description = replace(CAST(description AS NVARCHAR(MAX)),'../G/','http://styletint.in/G/') where description like '%../G/%'

SQL Split function

CREATE FUNCTION dbo.Split(@String VARCHAR(8000), @Delimiter CHAR(1)) 
RETURNS @temptable TABLE (items VARCHAR(8000)) 
AS 
BEGIN DECLARE @idx INT 
DECLARE @slice VARCHAR(8000) 
SELECT @idx = 1 
IF LEN(@String)<1 font="font" is="is" nbsp="nbsp" null="null" or="or" return="return" tring="tring">
WHILE @idx!= 0 
BEGIN 
SET @idx = CHARINDEX(@Delimiter,@String) 
IF @idx!=0 
SET @slice = LEFT(@String,@idx - 1) 
ELSE 
SET @slice = @String IF(LEN(@slice)>0) 
INSERT INTO @temptable(Items) VALUES(@slice) 
SET @String = RIGHT(@String,LEN(@String) - @idx) 
IF LEN(@String) = 0 
BREAK 
END 
RETURN 
END 

To get the names of the latest altered storedprocedures in SQL.

following is the query:
declare @date1 datetime
set @date1 = '1-sep-2008'

select specific_name,last_altered,created from information_schema.routines
where datediff(day,@date1, last_altered) > 0
or
datediff(day,@date1, created) > 0

get data from .txt file and insert into sql table.

protected void button_click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
DataColumn dc;
dc = new DataColumn("ACCOUNT", typeof(System.String));
dt.Columns.Add(dc);
dc = new DataColumn("CUSTOMER", typeof(System.String));
dt.Columns.Add(dc);
dc = new DataColumn("TRADES", typeof(System.String));
dt.Columns.Add(dc);
dc = new DataColumn("SHARES", typeof(System.String));
dt.Columns.Add(dc);
dc = new DataColumn("COMMISSION", typeof(System.String));
dt.Columns.Add(dc);
dc = new DataColumn("TRADES1", typeof(System.String));
dt.Columns.Add(dc);
dc = new DataColumn("SHARES1", typeof(System.String));
dt.Columns.Add(dc);
dc = new DataColumn("COMMISSION1", typeof(System.String));
dt.Columns.Add(dc);

string con = "Data Source=SERVERNAME;Database=DATABASENAME;Persist Security Info=True;User ID=USERNAME;Password=PWD";
SqlConnection myConnection = new SqlConnection(con);
const string strSQL = "select * from praveen";
SqlCommand myCommand = new SqlCommand(strSQL, myConnection);
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
SqlCommandBuilder cb = new SqlCommandBuilder(myAdapter);
myConnection.Open();

System.IO.StreamReader sr = new System.IO.StreamReader(@"C:/Documents and Settings/nitin.dhiman/Desktop/NYSE_MERGE_ORDER_LOG.doc");
string line;
int i = 0;
while (sr.Peek() != -1)
{
i++;
line = sr.ReadLine();
if (line.Contains("MTD TRADES"))
{
flage = 1;
}
if (line.Contains("/PAGE"))
{
flage = 0;
}
if (line.Contains("TOTALS"))
{
flage = 0;
}
if (flage == 1)
{
if (line == "" || line == " " || line == " ACCOUNT CUSTOMER TRADES SHARES COMMISSION TRADES SHARES COMMISSION " || line == "---------- ------------------------------ ---------- --------------- --------------- --------- --------------- --------------- " || line == " ---------- MTD TRADES ---------- ---------- MTD SETTLES ---------- ")
{

}
else
{
string value = line;
value = value.Replace(" ", "~");
value = value.Replace("~~~~~~~~~", "~");
value = value.Replace("~~~~~~~~", "~");
value = value.Replace("~~~~~~~", "~");
value = value.Replace("~~~~~~", "~");
value = value.Replace("~~~~~", "~");
value = value.Replace("~~~~", "~");
value = value.Replace("~~~", "~");
value = value.Replace("~~", "~");
string[] text = value.Split('~');

DataRow dr = dt.NewRow();
dr[0] = text[0];
dr[1] = text[1];
dr[2] = text[2];
dr[3] = text[3];
dr[4] = text[4];
dr[5] = text[5];
dr[6] = text[6];
dr[7] = text[7];
dt.Rows.Add(dr);

myAdapter.Update(dt);
dt.AcceptChanges();
}
}
}
Response.Write(i);
}

Bind TreeView to sql database.

Call the following function in page_load event:


void fill_Tree()
{
//Database db = DatabaseFactory.CreateDatabase("ConStr");
//string sqlCommandName = "Select * from ParentTable";
//DbCommand dbCmd = db.GetSqlStringCommand(sqlCommandName);
//SqlDataReader Sdr = dbCmd.ExecuteReader();

/ SqlCon = new SqlConnection("Data Source=DATABASENAME;Initial Catalog=GKTraining;Persist Security Info=True;User ID=USERID;Password=gK@t#a!n!^T)");

SqlCon.Open();

/*
* Query the database
*/

SqlCommand SqlCmd = new SqlCommand("Select * from a_tbl_parent", SqlCon);

/*
*Define and Populate the SQL DataReader
*/

SqlDataReader Sdr = SqlCmd.ExecuteReader();

/*
* Dispose the SQL Command to release resources
*/

SqlCmd.Dispose();

/*
* Initialize the string ParentNode.
* We are going to populate this string array with our ParentTable Records
* and then we will use this string array to populate our TreeView1 Control with parent records
*/

string[,] ParentNode = new string[100, 2];

/*
* Initialize an int variable from string array index
*/

int count = 0;

/*
* Now populate the string array using our SQL Datareader Sdr.

* Please Correct Code Formatting if you are pasting this code in your application.
*/

while (Sdr.Read())
{

ParentNode[count, 0] = Sdr.GetValue(Sdr.GetOrdinal("ParentID")).ToString();
ParentNode[count++, 1] = Sdr.GetValue(Sdr.GetOrdinal("ParentName")).ToString();

}

/*
* Close the SQL datareader to release resources
*/

Sdr.Close();

/*
* Now once the array is filled with [Parentid,ParentName]
* start a loop to find its child module.
* We will use the same [count] variable to loop through ChildTable
* to find out the number of child associated with ParentTable.
*/

for (int loop = 0; loop < count; loop++)
{

/*
* First create a TreeView1 node with ParentName and than
* add ChildName to that node
*/

TreeNode root = new TreeNode();
root.Text = ParentNode[loop, 1];
root.Target = "_blank";

/*
* Give the url of your page
*/

root.NavigateUrl = "mypage.aspx";

/*
* Now that we have [ParentId] in our array we can find out child modules

* Please Correct Code Formatting if you are pasting this code in your application.

*/

SqlCommand Module_SqlCmd = new SqlCommand("Select * from a_tbl_child where ParentId =" + ParentNode[loop, 0], SqlCon);

SqlDataReader Module_Sdr = Module_SqlCmd.ExecuteReader();

while (Module_Sdr.Read())
{

// Add children module to the root node

TreeNode child = new TreeNode();

child.Text = Module_Sdr.GetValue(Module_Sdr.GetOrdinal("ChildName")).ToString();

child.Target = "_blank";

//child.NavigateUrl = "your_page_Url.aspx";
child.NavigateUrl = Module_Sdr.GetValue(Module_Sdr.GetOrdinal("ChildName")).ToString()+".aspx";

root.ChildNodes.Add(child);

}

Module_Sdr.Close();

// Add root node to TreeView
TreeView1.Nodes.Add(root);

}

/*
* By Default, when you populate TreeView Control programmatically, it expends all nodes.
*/
TreeView1.CollapseAll();
SqlCon.Close();

}

from Excel file to Sql table.

Hello again,

Here is the code to fill sql table from data in excel file:

protected void button_click(object sender, EventArgs e)
{
string sqlTblName = "nitinSongs";
string excelFileName = "WT_codes.xls";
string workBook = "[nitin$]";
string exlConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("WT_codes.xls") + ";" + "Extended Properties=Excel 8.0;";
string sqlConStr = "Data Source=SERVER NAME;Database=DATABASE NAME;Persist Security Info=True;User ID=LOGINID;Password=PASSWORD";

SqlConnection sqlCon = new SqlConnection(sqlConStr);
SqlCommand sqlCom = new SqlCommand("DELETE FROM " + sqlTblName, sqlCon);
sqlCon.Open();
sqlCom.ExecuteNonQuery();
sqlCon.Close();

OleDbConnection oleCon = new OleDbConnection(exlConStr);
OleDbCommand oleCom = new OleDbCommand("select * from " + workBook, oleCon);
oleCon.Open();
OleDbDataReader dr = oleCom.ExecuteReader();
SqlBulkCopy bCopy = new SqlBulkCopy(sqlConStr);
bCopy.DestinationTableName = sqlTblName;
bCopy.WriteToServer(dr);
oleCon.Close();
}

Sql function to get first day of the week.

Hello pals,

How r you??

Today we are going to create a user defined function in SQL 2005, to fetch first day of the week.


GO
/****** Object: UserDefinedFunction [dbo].[F_START_OF_WEEK] Script Date: 02/05/2009 18:19:51 by Nitin Dhiman ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[F_START_OF_WEEK]
(
@DATE datetime,
– Sun = 1, Mon = 2, Tue = 3, Wed = 4
– Thu = 5, Fri = 6, Sat = 7
– Default to Sunday
@WEEK_START_DAY int = 1
)
/*
Find the fisrt date on or before @DATE that matches
day of week of @WEEK_START_DAY.
*/
returns datetime
as
begin
declare @START_OF_WEEK_DATE datetime
declare @FIRST_BOW datetime

– Check for valid day of week
if @WEEK_START_DAY between 1 and 7
begin
– Find first day on or after 1753/1/1 (-53690)
– matching day of week of @WEEK_START_DAY
– 1753/1/1 is earliest possible SQL Server date.
select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
– Verify beginning of week not before 1753/1/1
if @DATE >= @FIRST_BOW
begin
select @START_OF_WEEK_DATE =
dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
end
end

return @START_OF_WEEK_DATE

end