Tuesday 24 July 2012

Import CSV file into SQL Server

 

 

Importing data from csv file to SQL server db.

BULK INSERT TableName FROM 'csv file path' WITH(FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = ',' )

(Inserts csv file data to specified table Name )

 

FIRSTROW = 2                – Start import from this row

MAXERRORS = 0              – max number of syntax errors allowed

FIELDTERMINATOR = ','     –Char that separates Fields

There are some other parameters available like KEEPIDENTITY , KEEPNULLS , LASTROW , ROWTERMINATOR  etc.

http://msdn.microsoft.com/en-us/library/ms188365(v=sql.90).aspx

 

Just remember that the BULK INSERT command requires an extremely high level of permissions [ADMINISTER BULK OPERATIONS, a server-level permission], which you will be unable to grant in any application you build. You won’t notice this if you use sa, but sa is disabled on SQL2008 ;)

However, if you are doing an import from .NET code then the System.Data.SqlClient.SqlBulkCopy class is great, and needs far fewer permissions. If only they would give us the equivalent of SqlBulkCopy for exports too …

Sample Data:

D:\FileStore\names.csv

Title,Forename,Surname
Mr,John,Smith
Mrs,Jane,Smith
Miss,Elizabeth,Smith
Mr,James,Smith
Mr,Mathew,Smith
Mrs,Catherine,Smith
Miss,Mary,Smith
Mr,David,Smith

 

My Query :

CREATE TABLE [#FakeNames] (   
      Title VARCHAR(20),     
      Forename VARCHAR(50),
      Surname VARCHAR(50)
    )
BULK INSERT #FakeNames FROM 'D:\FileStore\names.csv' WITH(FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = ',' )

Cookies in JavaScript

http://www.thesitewizard.com/javascripts/cookies.shtml

How to Use Cookies in JavaScript

Cookies are bits of data that a browser stores in your visitor's computer. They are useful in that they allow you to store things like your visitor's preferences when they visit your site or other types of data specific to a particular user.

What Kinds of Data Can Be Stored in a Cookie?

A cookie is basically a string of text characters not longer than 4 KB. Cookies are set in name=value pairs, separated by semi-colons. For example, a cookie might be a string like the following:

"MyCookieName=myvalue; max-age==" + 60*60*24*30 + "; path=/; domain=example.com"

" MyCookieName " is the name of the cookie I want to use and set and contains the real data that I wish to set.

· max-age

Cookies have, by default, a lifespan of the current browser session. As soon as your visitor closes his browser, your cookie disappears. To make it last longer, you will need to set the max-age variable to contain the number of seconds you want the cookie to last.

For example, if you want your cookie to last 30 days, set it to 2,592,000. Actually instead of pre-calculating this and putting it into your script, you can have the JavaScript interpreter calculate it for you at run time.

· path

By default cookies are valid only for web pages in the directory of the current web page that stored them, as well as its descendants. That is, if a cookie is set by http://example.com/abc/webpage.html, it will be valid for http://example.com/abc/yet-another-page.html as well as http://example.com/abc/Sub-Folder/index.html, but not for http://example.com/index.html.

If you want the cookie to be valid in some other directory, say, http://example.com/special/, you will need to set the path variable to contain the value "/special". If you want the cookie to be valid everywhere on your site, set it to the root of your web directory, that is, "/".

· domain

Another special variable name that you may want to take note of is the domain variable. Cookies set in sub-domains like www.example.com will only be valid for that subdomain. If you want it to be valid for all sub-domains of example.com, you will need to set the domain to point to "example.com". The cookie will then be valid for "www.example.com", "blog.example.com", and whatever other subdomains that you may have.

Note that for security reasons, if your domain is example.com, browsers will not accept a cookie for a different domain, like google.com.

· secure

There's another variable that has special meaning: secure. This variable should not be assigned any value. Including it means that the cookie will only be sent if your visitor is visiting your website over a secure connection.

· expires

The expires variable is obsolete although still supported by today's browsers. Use the max-age variable instead, since it is easier to use. Be careful not to use "expires" as a variable name to store your data as well.

· No spaces, commas, semi-colons

Your cookie values cannot have any embedded whitespaces, commas or semi-colons. If you have any, they must be converted to its "encoded" equivalent. The easiest way to do this is to use the encodeURIComponent() function to encode it, and the decodeURIComponent() function to decode it when you read the cookie.

Expanding on my earlier example, if you want to set a "theme" variable to "blue theme", you can do it this way:

"Cookiename=" + encodeURIComponent("value") + "; max-age=" + 60*60*24*30 + "; path=/; domain=example.com"

· Cookie Limits

Although different browsers may implement different limits for cookies, the bare minimum that they are supposed to support is as follows:

    • Cookie length: 4 KB. The total length of your string, including all the variables with special meaning, should not be more than 4,096 characters.
    • Maximum number of cookies per web server: 20.
    • Total number of cookies supported by the browser: 300. This includes cookies stored by other websites.

How to Set a Cookie

Setting a cookie is extremely simple. Just assign the string you want for the cookie to the document.cookie property. For example, if I want to set the cookie given in my example above, I can simply include the following JavaScript code.

document.cookie =

"cookiename=" + encodeURIComponent("value") +

"; max-age=" + 60*60*24*30 +

"; path=/; domain=example.com" ;

How to Read a Cookie

Setting a cookie is great and all that, but a cookie is only useful if one can actually read what one has set previously.

To read a cookie, just read the string currently held in document.cookie. Since the string includes all the usual overhead for a cookie, like "max-age", "path" and "domain", you will need to parse the string to obtain the value you want. There are many ways to do this, such as splitting the string into separate tokens, using one of the substring search functions, or using regular expressions.

The following function allow you to easily get the cookie value you want by simply specifying the variable name.

function get_cookie ( cookie_name )

{

var cookie_string = document.cookie ;

if (cookie_string.length != 0) {

var cookie_value = cookie_string.match (

'(^|;)[\s]*' +

cookie_name +

'=([^;]*)' );

return decodeURIComponent ( cookie_value[2] ) ;

}

return '' ;

}

If get_cookie() cannot find the cookie, it will return an empty string. This may happen even if you have set a cookie, since the visitor may have deleted it, or alternatively, disabled cookie support in his/her browser.

How to Delete a Cookie

There are times when you may want to delete a cookie, such as when a visitor logs out of your site. To do this, set the max-age variable to 0 (zero) for the same cookie in the same path and domain.

 

 

Issues :

Setting cookie using JavaScript is working fine in Chrome and Firefox but IE deleting the cookies on exit even if expiry date is set to 100 years from now.

After researching a bit I came to know that it is not creating cookie with the expiry date as I send but just creating session cookies(without any expiry date and will be deleted once we close the browser).

I thought time format which i am using to set expiry date might not be acceptable to IE.

So I changed it to differed format(GMT format).

Also when I am trying to read cookies using normal array and split Firefox is not recognising my cookie for some reason.I have to use the substring method to get my cookie.

Check my code :

        function ReadCookie(cookieName) {
            var allcookies = document.cookie;
            // Get all the cookies pairs in an array
            cookiearray = allcookies.split(';');
            // Now take key value pair out of this array
            for (var i = 0; i < cookiearray.length; i++) {
                var value = '';
                var name = '';
                name = cookiearray[i].split('=')[0];
                value = cookiearray[i].split('=')[1];
                var searchFor = cookieName + "=";
                //alert("Key is : " + name + " and Value is : " + value);
                var start = document.cookie.indexOf(searchFor) + searchFor.length; //Bad coding style but i didn't find any other way at that time. Annoyed
                var end = document.cookie.indexOf(";", start);
                if (end == -1) {
                    end = document.cookie.length;
                }
                value = document.cookie.substring(start, end);
                //alert(cookieName == name);
                if (value == "true") {
                    //if (cookieName == name) { //Not working in Firefox Sad smile
                    return value;
                } else {
                }
            }
            return null;
        }
      

        $(document).ready(function () {

            if (ReadCookie("bReadCookiesInfo") != null) {
            } else {
                $('#divCookieInfo').fadeIn('slow', function () {
                });
                var expireDate = new Date()
                expireDate.setTime(new Date().getTime() + 60 * 60 * 24 * 365 * 100) //1000 because time is in milliseconds , 100 -NO OF YEARS

                setCookie("bReadCookiesInfo", true, expireDate, '');
            }

            });

        function setCookie(name, value, expirydate, domain) {
            document.cookie = name + "=" + value + ";expires=" + expirydate.toGMTString() + ";path=/;";
        }

Tuesday 17 July 2012

Random selection of Rows and update random record in a table

 

CREATE TABLE Table1
(
    Column1 INT IDENTITY(1,1) PRIMARY KEY,
    Column2 VARCHAR(20) NOT NULL,
    Column3 VARCHAR(50) NOT NULL,
    Column4 VARCHAR(50) NOT NULL
)

INSERT INTO Table1 VALUES('Mr','AAA','111')
INSERT INTO Table1 VALUES('Mrs','BBB','222')
INSERT INTO Table1 VALUES('Miss','CCC','333')
INSERT INTO Table1 VALUES('Mr','DDD','444')
INSERT INTO Table1 VALUES('Mrs','EEE','555')

CREATE TABLE Table2
(
    Col1 INT IDENTITY(1,1) PRIMARY KEY,
    Col2 VARCHAR(20) NOT NULL,
    Col3 VARCHAR(50) NOT NULL,
    Col4 VARCHAR(50) NOT NULL
)


INSERT INTO Table2 VALUES('','','')
INSERT INTO Table2 VALUES('','','')
INSERT INTO Table2 VALUES('','','')
INSERT INTO Table2 VALUES('','','')
   
DECLARE @Column1 INT   
DECLARE @Column2 VARCHAR(20)
DECLARE @Column3 VARCHAR(50)
DECLARE @Column4 VARCHAR(50)

SELECT TOP 1
    @Column1 = Column1 ,
    @Column2 = Column2 ,
    @Column3 = Column3 ,
    @Column4 = Column4
FROM
    Table1
ORDER BY NEWID()
    
UPDATE Table2
SET
    Col2 = @Column2 ,
    Col3 = @Column3 ,
    Col4 = @Column4       
FROM   
    Table2 T1
    INNER JOIN
        (
            SELECT TOP 1 Col1 FROM Table2 ORDER BY NEWID()
        ) T2 ON T2.Col1 = T1.Col1