Menu
The go to solution for removing duplicate rows from your result sets is to include the distinct keyword in your select statement. It tells the query engine to remove duplicates to produce a result set in which every row is unique.
While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records. The SQL DISTINCT keyword, which we have already discussed is used in conjunction with the SELECT statement to eliminate all the duplicate records and by fetching only the unique records.
Once you have grouped data you can filter out duplicates by using having clause. Having clause is the counterpart of where clause for aggregation queries. Just remember to provide a temporary name to count() data in order to use them in having clause.
From the SQL Server Management Studio you can right click on your database and select:
Tasks -> Generate Scripts
Then simply proceed through the wizard. Make sure to set 'Script Data' to TRUE when prompted to choose the script options.
Further reading:
This is your best tool for performing this task. You can generate a script that will build whichever tables you wish from a database as well as insert the data in those tables (as far as I know you have to export all of the data in the selected tables however).
To do this follow these steps:
To restore your database, simply create a new database and change the first line of your generated script to USE [Your.New.Database.Name], then execute. Your new database will now have all of the tables and data you selected from the original database.
I had a hell of a time finding this option in SQL Management Studio 2012, but I finally found it. The option is hiding in the Advanced button in the screen below.
I always assumed this contained just assumed advanced options for File generation, since that's what it's next to, but it turns out someone at MS is just really bad at UI design in this case. HTH somebody who comes to this thread like I did.
If you want to script all table rows then Go with Generate Scripts as described by Daniel Vassallo. You can’t go wrong here
Else Use third party tools such as ApexSQL Script or SSMS Toolpack for more advanced scripting that includes some preprocessing, selective scripting and more.
Check out SSMS Tool Pack. It works in Management Studio 2005 and 2008. There is an option to generate insert statements which I've found helpful moving small amounts of data from one system to another.
With this option you will have to script out the DDL separately.
SqlPubWiz.exe (for me, it's in C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2>)
Run it with no arguments for a wizard. Give it arguments to run on commandline.
SqlPubWiz.exe script -C "<ConnectionString>" <OutputFile>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With