How to generate script for data present in database

This tool works for only Microsoft SQL Server 2000 / 2005 Only.

Open the command prompt. Go to the following folder. This folder may vary based on where you installed Sql Server.

C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2

Type the following at the command prompt and press enter. This will take Windows Authentication. Change the databasename and file path as required.

sqlpubwiz script -d DatabaseName C:\FileName.sql

Type the following at the command prompt and press enter. The following command will script the FooDB database from the default instance on a machine named MYSERVER using SQL Server authentication with the username "Alice" and the password "7h92-v6k3" to the file C:\FooDB.sql:

sqlpubwiz script -d FooDB -S MYSERVER -U Alice -P 7h92-v6k3 C:\FooDB.sql


You can also check another free tool "SQL Scripter" at the following site.

http://www.sqlscripter.com/

How to eliminate duplicate rows in a table



/*Create table DupRecords*/
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME='DUPRECORDS' AND t.TABLE_SCHEMA='dbo')
BEGIN
Create Table dbo.DupRecords
(
id INT not null
,Name varchar(40)
,Salary money
)
END

/*Insert duplicate data*/
insert into DupRecords values
(1,'A', 1000)
,(1,'A', 1000)
,(2,'B',2000)
,(3,'C',3000)
,(2,'B',2000)
,(2,'B',2000)
,(3,'C',3000)
,(4,'D',4000)


If we want to eliminate duplicate rows in a table use the following syntax


/*Display Non duplicate data*/
SELECT distinct * FROM dbo.duprecords


The following code is used to eliminate duplicate rows based on selected columns by displaying all the columns. The columns on which duplicate is decided need to be mentioned after Partition by in the Over() function


SELECT Id,Name,Salary FROM
(
select Id,Name,Salary, ROW_NUMBER()over(partition by id,name,salary order by id) as [RowNumber] from dbo.DupRecords
) t
WHERE RowNumber=1


If we want to display what are the records we need to delete to eliminate duplicate rows in a table we can use the following sql statement.

SELECT Id,Name,Salary FROM
(
select *, ROW_NUMBER()over(partition by id,name,salary order by id) as [RowNumber] from dbo.DupRecords
) t
WHERE RowNumber>1