Generate insert statements from the specific table data using SQL Server

0

When you are deploying your application, sometimes you may want to generate SQL script with the insert statement of the specific database table. In this article I will show you two easiest ways to do this with SQL Server and SQL Server tools.

1. Using Database Publishing Wizard (“SqlPubWiz.exe”)

If you install Visual studio 2008 / Visual Studio 2008 Express full version, it will automatically install the Microsoft SQL Server Data Publishing Wizard on the following location.
[SQL Server Installation Location]\90\Tools\Publishing\[version]\ SqlPubWiz.exe

If it is not there you can download it from the following URL
http://go.microsoft.com/fwlink/?LinkId=119368

SQLPubWiz location

SQLPubWiz location

When you run the “SqlPubWiz.exe”, it will open the database publishing wizard

Database Publishing Wizard

Database Publishing Wizard

Click next and enter the database server connection information accordingly.

Database Publishing Wizard - Database Server Connection

Database Publishing Wizard – Database Server Connection

Click next, and it will ask you to select the database, which you want to generate the data from

Database Publishing Wizard - Select Database

Database Publishing Wizard – Select Database

Next screen will ask to select the Object type. You can use this database publishing wizard to generate data and schema both. In here I consider only for the data generation part. Now choose “Tables” as an option and click next. (Make sure to uncheck “Script all objects in the selected database”)

Database Publishing Wizard - Choose Object Type

Database Publishing Wizard – Choose Object Type

Then, the next ask to select the table which you want to generate the data from. Select whatever the table you want and click next.

Database Publishing Wizard - Choose Tables

Database Publishing Wizard – Choose Tables

Next step ask to specify the output location. You can enter the file name and the location there.

Database Publishing Wizard - Select an Output Location

Database Publishing Wizard – Select an Output Location

By clicking Next, It shows to select the publishing options. There are three publishing types, Schema only, Data only, or Schema and data. Here you have to select “Data only”.

Database Publishing Wizard - Select Publishing Option

Database Publishing Wizard – Select Publishing Option

Click next and it will generate the insert data script for you.

Database Publishing Wizard - Publishing Progress

Database Publishing Wizard – Publishing Progress

Database Publishing Wizard - Generated Script

Database Publishing Wizard – Generated Script

2. Using SQL Server Generate Script wizard

SQL Server Generate Script

SQL Server Generate Script

When you select “Generate Script” menu item, it will open the Generate SQL Server Scripts Wizard dialog.

SQL Server Generate Script - Wizard Dialog

SQL Server Generate Script – Wizard Dialog

By clicking next, it asks to select the database which you want to generate the script.

SQL Server Generate Script Wizard - Select Database

SQL Server Generate Script Wizard – Select Database

By clicking Next, It shows to select the script options dialog box. In here, you change the “Script Data” as “True” and click the next button

SQL Server Generate Script Wizard - Choose Script Option

SQL Server Generate Script Wizard – Choose Script Option

Next screen will ask to select the Object type. Now choose “Tables” as an option and click next.

SQL Server Generate Script Wizard - Choose Object Type

SQL Server Generate Script Wizard – Choose Object Type

Next screen ask you to select the tables which you want to generate the data from. Select whatever the tables and click next

SQL Server Generate Script Wizard - Choose Tables

SQL Server Generate Script Wizard – Choose Tables

Next step ask to specify the output options. If you select “Script to file” as an option, then you can enter the file name and the location there.

SQL Server Generate Script Wizard - Output Option

SQL Server Generate Script Wizard – Output Option

Next two steps are very strait forward. It shows the script generation summary, and by clicking next it will generate the script with insert statements for the selected tables.

SQL Server Generate Script Wizard - Script Summary

SQL Server Generate Script Wizard – Script Summary

SQL Server Generate Script Wizard - Script Progress

SQL Server Generate Script Wizard – Script Progress

SQL Server Generate Script Wizard - Generated Script

SQL Server Generate Script Wizard – Generated Script

How to Fix Orphaned SQL Users

13

Recently I had to involve in a database migration task. I backed up the database and restored successfully in the new server. However when I access the database through the web site, database login didn’t work. The problem was even though the database user is included in the restored database; the login information was not there. So I re-created the login in the server, but it also didn’t work.

This phenomenon is called “orphaned users“.

Database with orphaned users

Details of the Problem:

User logon information is stored in the syslogins table in the master database. By changing servers, or by altering this information by rebuilding or restoring an old version of the master database, the information may be different from when the user database dump was created. If logons do not exist for the users, they will receive an error indicating “Login failed” while attempting to log on to the server. If the user logons do exist, but the SID (secure identifier) in master..syslogins and the sysusers table in the user database differ, the users may have different permissions than expected in the user database. (Microsoft et.al)

User login information in syslogins table in the master database

User login information in syslogins table in the master database

User information in the sysusers table in the user database

User information in the sysusers table in the user database

See above example, both tables contain the different SID for the username “eyepax”

How to fix:

1. The easiest way to fix this is delete the user from the restored database and then create and setup the user & corresponding permission to the database.

2. If the user owns a schema in the database, you won’t be able to delete the user. Then you can use the special stored procedure “sp_change_users_login”.

Syntax

sp_change_users_login [ @Action = ] action
[ , [ @UserNamePattern = ] user]
[ , [ @LoginName = ] login]
[ , [ @Password = ] password]

Arguments

[@Action =] action

Following are the list of actions can be performed by the procedure.

Value Description
Auto_Fix Links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins. You should check the result from theAuto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When using Auto_Fix, you must specify user and password; login must be NULL. user must be a valid user in the current database.
Report Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login.
user, login, and password must be NULL or not specified.
Update_One Links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified.

To perform the operations, first you have to select the database which contained the issues. Here are some of the operations you can perform.

  • Lists the orphaned users
EXEC sp_change_users_login 'Report'
Lists the orphaned users

Lists the orphaned users

  • If you already create a SQL server with same login information and if you want to map that with the database user
EXEC sp_change_users_login 'Auto_Fix', 'user'
Auto fix orphaned user

Auto fix orphaned user

3. MSDN says, maps an existing database user to a SQL Server login. sp_change_users_login feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.

Syntax

ALTER USER userName
WITH <set_item> [ ,...n ]

<set_item> ::=
NAME = newUserName
| DEFAULT_SCHEMA = schemaName
| LOGIN = loginName

Argument

userName -Specifies the name by which the user is identified inside this database.
LOGIN =loginName, Re-maps a user to another login by changing the user’s Security Identifier (SID) to match the login’s SID.
NAME =newUserName, Specifies the new name for this user. newUserName must not already occur in the current database.
DEFAULT_SCHEMA =schemaName, Specifies the first schema that will be searched by the server when it resolves the names of objects for this user.

ALTER USER UserName WITH LOGIN = UserName
Alter user

Alter user

For more information

sp_change_users_login
http://msdn.microsoft.com/en-us/library/ms174378(v=sql.110).aspx

ALTER USER
http://msdn.microsoft.com/en-us/library/ms176060.aspx

Troubleshoot Orphaned Users (SQL Server)
http://msdn.microsoft.com/en-us/library/ms175475.aspx

Online tools for troubleshooting web, SQL, XML and JSON

1

Have you ever wanted to troubleshoot someone else web page which contains the combination of JavaScript, CSS and HTML? Have you ever wanted to troubleshoot someone else SQL query without entering to the SQL server? Here are some useful online tool which make your life easier.

Following tools provide an online debugging environment for HTML, CSS and JavaScript.

1. jsFiddle

jsFiddle is the most popular JavaScript sandbox. It provides instantly ready coding environment for you to begin experimenting in as soon as the page loads.

http://jsfiddle.net/

JS Fiddle

2. JS Bin

JS Bin is another JavaScript sandbox. The design of this app is quite minimal and really allows you to focus on the code. By default, the page only shows the HTML and lives preview panes, but you can easily add JavaScript to that as well.

http://jsbin.com/

JS Bin

3. CSSDesk

CSSDesk is a CSS sandbox which allows you to put your HTML and CSS codes there and view the preview instantly. It is very useful for testing out codes before implementing them in your website permanently.

http://cssdesk.com/

CSS Desk

4. CodePen

CodePen is an app for sharing and playing around with front end code. It’s a site for building stuff from HTML, CSS and JavaScript.

http://codepen.io/pen/

Code Pen

Following tool can use for easy online testing and sharing of database problems and solutions.

1. SQL Fiddle

SQL Fiddle is a tool for easy online testing and sharing of database problems and their solutions. If you do not know SQL or basic database concepts, this site is not going to be very useful to you. However, if you are a database developer, there are a few different use-cases of SQL Fiddle intended for you:

  • You want help with a tricky query, and you’d like to post a question to a Q/A site
  • You want to compare and contrast SQL statements in different database back-ends
  • You do not have a particular database platform readily available, but you would like to see what a given query would look like in that environment

http://sqlfiddle.com/

SQL Fiddle

Following tools act as sandbox environment for JSON,  XML development, including (E)XSLT, XML validation (DTD, schema, RelaxNG) and XQuery.

1. XMLPlayground

XMLPlayground is a sandbox environment for XML development, including (E)XSLT, XML validation (DTD, schema, RelaxNG) and XQuery. It has five development panels into which you can either insert or upload code:

  • XML – your source XML. You can render it as a node tree via the ‘tree’ tab.
  • (E)XSLT – transform your XML with XSLT (1.0). EXSLT an XSL includes are supported.
  • Validation – validate your XML with Schema, DTD or RelaxNG.
  • CSS – style your output
  • XQuery – query or transform your XML with XQuery code (via XQIB)

http://xmlplayground.com/

XML Playground

2. JSON Fiddle

With JSON Fiddle, simulating and posting JSON in Fiddler is just as easy.

http://jsonfiddle.net

json fiddle

Counting Consecutive Dates Using SQL

0

Download demo query

Recently I have answered one of the SQL based question in the CodeProject. Thanks for the participant that question wake up me to write this blog post.

Question:

He has a table (tblLeave) with the data like below.

PAYCODE		LV_TYPE	FROM_DATE		TO_DATE	        	LVALUE
5023		SL    	14/12/2012 0:00		14/12/2012 0:00		1
5023		SL    	15/12/2012 0:00		15/12/2012 0:00		1
5023		COF   	16/12/2012 0:00		16/12/2012 0:00		1
5023		SL    	19/12/2012 0:00		19/12/2012 0:00		1
5023		SL    	22/12/2012 0:00		22/12/2012 0:00		1
5023		SL    	23/12/2012 0:00		23/12/2012 0:00		1
5023		SL    	24/12/2012 0:00		24/12/2012 0:00		1
5023		PL    	28/12/2012 0:00		28/12/2012 0:00		1
5023		PL    	29/12/2012 0:00		29/12/2012 0:00		1
5023		PL    	30/12/2012 0:00		30/12/2012 0:00		1
5023		PL    	31/12/2012 0:00		31/12/2012 0:00		1

And he wants to output the data as below

PAYCODE LV_TYPE FROM_DATE       TO_DATE         LVALUE
5023    SL      14/12/2012 0:00 15/12/2012 0:00 2
5023    COF     16/12/2012 0:00 16/12/2012 0:00 1
5023    SL      19/12/2012 0:00 19/12/2012 0:00 1
5023    SL      22/12/2012 0:00 24/12/2012 0:00 3
5023    PL      28/12/2012 0:00 31/12/2012 0:00 4

Condition: If same type of leave taken continuously, it should be merged in one row mentioning from_date to to_date.

Answer:

When I saw that question first time, I didn’t go through in details and just thought that was an easy grouping query. And I just gave following answer.

SELECT LV_TYPE,LV_TYPE,MIN(FROM_DATE) AS FROM_DATE ,MAX(TO_DATE) AS TO_DATE, COUNT(LVALUEP) AS LVALUE
FROM tblLeave
GROUP BY PAYCODE,LV_TYPE

But that’s wrong, he made comments saying it doesn’t make sense and highlighted the condition he wants. (Thanks for him; he didn’t down vote my answer). Again I read the question… Oh… That was a tricky question. He needs to group the leave by consecutive date. Isn’t that tricky?

To answer that, I use the DATEDIFF SQL function

DATEDIFF ( datepart , startdate , enddate )

http://msdn.microsoft.com/en-us/library/ms189794.aspx

Following is my answer and the output

SELECT PAYCODE,LV_TYPE, MIN(FROM_DATE) AS FROM_DATE,
       MAX(FROM_DATE) AS TO_DATE, COUNT('A') AS LVALUE
FROM (
SELECT PAYCODE,LV_TYPE,FROM_DATE,
    DATEDIFF(D, ROW_NUMBER() OVER(ORDER BY FROM_DATE), FROM_DATE) AS Diff
FROM tblLeave) AS dt
GROUP BY PAYCODE,LV_TYPE, Diff
ORDER BY FROM_DATE
PAYCODE     LV_TYPE FROM_DATE               TO_DATE                 LVALUE
----------- ------- ----------------------- ----------------------- -----------
5023        SL      2012-12-14 00:00:00.000 2012-12-15 00:00:00.000 2
5023        COF     2012-12-16 00:00:00.000 2012-12-16 00:00:00.000 1
5023        SL      2012-12-19 00:00:00.000 2012-12-19 00:00:00.000 1
5023        SL      2012-12-22 00:00:00.000 2012-12-24 00:00:00.000 3
5023        PL      2012-12-28 00:00:00.000 2012-12-31 00:00:00.000 4

Query Explanation:

Before explain the logic see the following query and the output.

SELECT PAYCODE,LV_TYPE,FROM_DATE,
	ROW_NUMBER() OVER(ORDER BY FROM_DATE) AS ROW_NUMBER,
    DATEDIFF(D, ROW_NUMBER() OVER(ORDER BY FROM_DATE), FROM_DATE) AS Diff
FROM tblLeave
PAYCODE     LV_TYPE FROM_DATE               ROW_NUMBER           Diff
----------- ------- ----------------------- -------------------- -----------
5023        SL      2012-12-14 00:00:00.000 1                    41254
5023        SL      2012-12-15 00:00:00.000 2                    41254
5023        COF     2012-12-16 00:00:00.000 3                    41254
5023        SL      2012-12-19 00:00:00.000 4                    41256
5023        SL      2012-12-22 00:00:00.000 5                    41258
5023        SL      2012-12-23 00:00:00.000 6                    41258
5023        SL      2012-12-24 00:00:00.000 7                    41258
5023        PL      2012-12-28 00:00:00.000 8                    41261
5023        PL      2012-12-29 00:00:00.000 9                    41261
5023        PL      2012-12-30 00:00:00.000 10                   41261
5023        PL      2012-12-31 00:00:00.000 11                   41261

By seen this, you will realize that above query generate the same Diff  value for all the consecutive dates. Now you can easily group this and get the counts as you like.

Generating and splitting a delimited string column

1

Download demo query – 1.6 KB

Introduction

Recently I have answered one of the Code Project questions, in brief the question was, how to split the delimited string and return as row values. This is one of the common questions we all are facing in our development. Not only splitting the delimited string, but also we struggle when creating the delimited strings using SQL.

In this article, I will show you how easily this can be done by using the SQL XQuery.

Creating delimited strings

Suppose we have a table called DeveloperProject to store the individual developer project list.

DECLARE @DeveloperProject Table(
 DeveoperID INT,
 ProjectCode VARCHAR(20)
)
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'CODE_PROJECT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'MSFT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(1,'REPT_MOD')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(2,'MSFT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(2,'CODE_PROJECT')
INSERT INTO @DeveloperProject(DeveoperID, ProjectCode) VALUES(3,'REPT_MOD')

Now we need to convert this as follows

DeveoperID  ProjectCodes
----------- ------------------------
1           CODE_PROJECT,MSFT,REPT_MOD
2           MSFT,CODE_PROJECT
3           REPT_MOD

There are many ways to do this. But here is the easiest way.

SELECT DeveoperID,
    REPLACE((SELECT ProjectCode AS'data()'
        FROM @DeveloperProject dp2
        WHERE dp2.DeveoperID = dp1.DeveoperID
    FOR XML PATH('')),' ',',')AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID
Instead of REPLACE function you can also use the SUBSTRING function as below
SELECT DeveoperID,
    SUBSTRING((SELECT ','+ ProjectCode
        FROM @DeveloperProject dp2
        WHERE dp2.DeveoperID = dp1.DeveoperID
    FOR XML PATH('')), 2, 8000)AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID
Table with delimited column

FOR XML() clause

A SELECT query returns results as a rowset. You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query.
http://msdn.microsoft.com/en-us/library/ms178107.aspx

data() function

If you want to get the value of an attribute, rather than the attribute node itself, we can use the data() method.

For ex: if you execute the following query, you will get the xml output with <ProjectCode> attribute nodes.

SELECT ProjectCode
FROM @DeveloperProject c2
FOR XML PATH('')

XML PATH with Nodes
Now run it as follows, and see the result set. It gives the output without <ProjectCode> nodes.

SELECT ProjectCode AS 'data()'
FROM @DeveloperProject c2
FOR XML PATH('')

XML PATH without Nodes

Splitting delimited string

Suppose we have a table called DeveloperProjectCSV to store the developer id and comma separated project list

DECLARE @DeveloperProjectCSV Table(
    DeveoperID INT,
    ProjectCodes  VARCHAR(MAX)
)

We can use previously created script to insert the data to this table.

INSERT INTO @DeveloperProjectCSV(DeveoperID,ProjectCodes)
SELECT DeveoperID,
    REPLACE((SELECT
        ProjectCode AS 'data()'
     FROM @DeveloperProject dp2
     WHERE dp2.DeveoperID = dp1.DeveoperID
     FOR XML PATH('')), ' ', ',') AS ProjectCodes
FROM @DeveloperProject dp1
GROUP BY DeveoperID

Now the table contains following data

DeveoperID  ProjectCodes
----------- ------------------------------
1           CODE_PROJECT,MSFT,REPT_MOD
2           MSFT,CODE_PROJECT
3           REPT_MOD

And we need to split the project codes for each developer.

;WITH cte AS (
    SELECT
        DeveoperID,
        CAST('' + REPLACE(ProjectCodes, ',', '') + '' AS XML) AS ProjectCodes
    FROM @DeveloperProjectCSV
)
SELECT
    DeveoperID,
    xTable.xColumn.value('.', 'VARCHAR(MAX)') AS ProjectCode
FROM cte
CROSS APPLY ProjectCodes.nodes('//r') AS xTable(xColumn)

Splited string

WITH (common_table_expression)

WITH is used to specify the temporary named result set. In above WITH block creates temporary results set with two columns. One is a DeveloperID and other one is an xml type column for ProjectCodes
http://msdn.microsoft.com/en-us/library/ms175972.aspx

nodes() Method

The nodes() method is useful when you want to share an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.

nodes (XQuery) as Table(Column)

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

CROSS APPLY

CROSS APPLY is just like an inner join query clause which allows joining between two table expressions.
http://technet.microsoft.com/en-us/library/ms175156.aspx


Creating delimited strings with SQL 2000

Since SQL 2000 doesn’t support XML PATH, above query doesn’t work with SQL 2000. As an alternative option we can use COALESCE or ISNULL function and create User Defined Function to get the concatenation output.
Suppose we have a table called “DeveloperProject” with following data inserted.

DeveloperID ProjectCode
----------- --------------------
1           CODE_PROJECT
1           MSFT
1           REPT_MOD
2           MSFT
2           CODE_PROJECT
3           REPT_MOD

First we have to create the concatenation function

CREATE FUNCTION dbo.rowToCSV(@DeveoperID int )
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @ProjectCodes VARCHAR(MAX)
    SELECT @ProjectCodes = COALESCE(@ProjectCodes + ', ', '') + ProjectCode
    FROM DeveloperProject
    WHERE DeveoperID = @DeveoperID
    RETURN @ProjectCodes
END

By using the above function, you will can get the same output.

SELECT
DeveoperID,
ProjectCodes = dbo.rowToCSV(DeveoperID )
FROM DeveloperProject
GROUP BY DeveoperID

Delimited String

COALESCE and ISNULL

COALESCE returns the first nonnull expression among its arguments. This is just like ISNULL function in SQL. Both ISNULL and COALESCE can be used to get the same results but there are some differences. http://msdn.microsoft.com/en-us/library/ms190349(v=sql.90).aspx

Splitting delimited string with SQL 2000
Again, SQL 2000 doesn’t support WITH (Common table expression) and the APPLY operators. As an alternative option we can use the spt_values master table.

SELECT  a.DeveloperID,
SUBSTRING(',' + a.ProjectCodes + ',', n.Number + 1, CHARINDEX(',', ',' + a.ProjectCodes + ',', n.Number + 1) - n.Number - 1) AS [Value]
FROM DeveloperProjects AS a
INNER JOIN master..spt_values AS n ON SUBSTRING(',' + a.ProjectCodes + ',', n.Number, 1) = ','
WHERE n.Type = 'p'
    AND n.Number > 0
    AND n.Number < LEN(',' + a.ProjectCodes + ',')

spt_values

spt_values is an undocumented system table, which contains various data items used by Microsoft written stored procedures. We can use the rows with type = ‘P’ to have a numbers table with the numbers from 1-2047.

Comparing time part only without comparing date part in SQL

0

Recently one of my colleagues asked how to compare only the time part of the DateTime field. I have seen many of us struggling to find an answer for this (Winking smile including me).

If you are one of them, here is a simple solution…

DECLARE @date_to_compare AS DATETIME = '2010-08-08 05:00:00';
DECLARE @date_start AS DATETIME = '2012-08-21 7:00:00';
DECLARE @date_end AS DATETIME = '2012-10-07 13:00:00';

SELECT 'OK'
WHERE  DATEADD(day, -DATEDIFF(day, 0, @date_to_compare), @date_to_compare)
BETWEEN DATEADD(day, -DATEDIFF(day, 0, @date_start), @date_start) AND DATEADD(day, -DATEDIFF(day, 0, @date_end), @date_end)

Above solution work as follows,
It converts the date part of the entered date in to the SQL default date, and then compares it.

SELECT DATEADD(day, -DATEDIFF(day, 0, @date_to_compare), @date_to_compare)
>> 1900-01-01 05:00:00.00