Just about a year ago, I had written on the subject of how to insert data from one table to another table without generating any script or using wizard in my article SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE. Today, we will go over a similar question regarding how to generate script for data from database as well as table. SQL Server 2008 has simplified everything.
Let us take a look at an example where we will generate script database. In our example, we will just take one table for convenience.
Right Click on Database >> Tasks >> Generate Scripts >>
This will pop up Generate SQL Server Scripts Wizards >> Click on Next >> Select Database >> This will bring up a screen that will suggest to Choose Script Option.
On Choose Script Option Screen of Script Wizard under section Table/View Options Look at Script Data row and Turn the Option to True.
The Next Screen will ask about object types. Select all the objects that are required in the generated script. Depending on the previous screen it will show few more screen requesting details about the objects required in script generation.
On the very last screen it will request output options. Select the desired output options of Script to file, Script to Clipboard or Script New Query Window.3
Clicking on Finish button will generate a review screen containing the required objects along with script generating data.
Clicking on Finish button one more time will generate the requested output.
Similarly, if you want to generate data for only one table, you can right click on the table and follow almost a similar wizard. I am sure this neat feature will help everybody who has been requesting for it for a long time.
Watch SQL in Sixty Seconds Episode on same subject.
[youtube=http://www.youtube.com/watch?v=lSh3fq-MikE]Reference : Pinal Dave (https://blog.sqlauthority.com)
137 Comments. Leave new
in Sql Server 2000 and Sql Server 2005 this kind of utility is not available so here is a way to do so
SELECT * into TabInstMaster FROM OPENDATASOURCE( ‘SQLOLEDB’, ‘Data Source=Servername IP\Name;User ID=USERNAME; Password= PASSWORD’ ).fullyqualifiedtablepath(db.owner.table)
Nice, new feature in sql2008 That’s not possible in sql2000 or sql2005,
In past when i want to transfer data from one server to another server i find the tool which create the script like this,
but in sql2008 it’s very simple now as you explain this functionality.
Dear Mr. Pinaldave,
I am happy to see this feature in 2008.
Thank you very much for Introduced
Gangadhar.
Ashish,
Does the script above generate the ‘INSERT SCRIPT’ for a table? (so that it can be sent to some one over email and can be executed in SSMS to recreate data)?
I found this feature added in SSMS 2008 very interesting. In fact, it is something MYSQL had several years back.
In SQL Server 2000/2005, I used to write a simple dirty query when I wanted to move data quickly from one location to another. Well, it is handy only when you need something quickly and the table has lesser number of columns.
A query like the following can generate an INSERT script for a given table.
SELECT ‘
INSERT INTO Customers(CustomerID, CustomerName)
SELECT ‘ + CAST(CustomerID AS VARCHAR) +
‘,”’ + CustomerName + ””
FROM Customers
/*
INSERT INTO Customers(CustomerID, CustomerName)
SELECT 1,’Jacob’
INSERT INTO Customers(CustomerID, CustomerName)
SELECT 2,’Pinal’
INSERT INTO Customers(CustomerID, CustomerName)
SELECT 3,’Binoy’
INSERT INTO Customers(CustomerID, CustomerName)
SELECT 4,’Sreejumon’
*/
Hi Pinal,
Very good Feature in 2008, Thanks for posting it
Hi Pinal,
Nice feature indeed, use it quite a bit. One of things to be noted is that in the Option Script for Server Version, there are choices for Sql server 2005 and Sql Server 2000. This provides for backward compatability. I found this very useful since we have databases on both SQL Server 2005 and SQL Server 2008.
Thank you
It is a pity the SQL Server 2008 wizard does not resort to row value constructors yet.
Pinal,
Nice to see the feature integrated within the database.
In SQL 2000 and SQL 2005 the same thing could be achieved using SQL Server Database Publishing Wizard. Link here,
http://www.microsoft.com/en-us/download/details.aspx?id=5498
I used to use this tool to generate data from SQL 2005 using the option in Server Explorer from VS 2005.
Thanks for sharing
There is a cool free tool from SQLTeam known as Scriptio which lets you choose the objects of interest to be Scripted.
I guess that will also do the necessary
Thanks for really nice information.
You always rock..
@Jacob Sir
No this one not generate insert scripts yea but i use it when i want to move data over two servers and so. i have a procedure for this which needs a table in your DB which stores information about the other server and the tables which we going to send over two servers. I will send my script to Pinal sir, so if he wish to upload it on blog so this is useful to people who working on SQL Server 2000 and SQL Server 2005. :)
i will also try to write a script which create all the insert startements and create statements for all objects.
Very good Ashish, I look forward to see your script.
Hello Sir
I want to copy table structure & its data in Sql Server 2005.
How i will do?
Please help me.
Jignesh,
One option is the Database Publishing Wizard posted by Ponnu.
If you have the Management Studio Of SQL Server 2008, you can connect to a SQL Server 2005 instance and can generate the schema and data for a SQL Server 2005 database.
regards
Jacob
@Jignesh
You can simply generate Scripts for table through SSMS by right clicking on your table and Script Create to in this way you can .. and if you want to script out all the tables in your database then here is a script for that i find it some where on internet and i am still struggling for generate Insert Scripts
so soon you will be find out some thing till then here is script to all create tables…
select ‘create table [‘ + so.name + ‘] (‘ + o.list + ‘)’ + CASE WHEN tc.Constraint_Name IS NULL THEN ” ELSE ‘ALTER TABLE ‘ + so.Name + ‘ ADD CONSTRAINT ‘ + tc.Constraint_Name + ‘ PRIMARY KEY ‘ + ‘ (‘ + LEFT(j.List, Len(j.List)-1) + ‘)’ END
from sysobjects so
cross apply
(SELECT
‘ [‘+column_name+’] ‘ +
data_type + case data_type
when ‘sql_variant’ then ”
when ‘text’ then ”
when ‘decimal’ then ‘(‘ + cast(numeric_precision_radix as varchar) + ‘, ‘ + cast(numeric_scale as varchar) + ‘)’
else coalesce(‘(‘+case when character_maximum_length = -1 then ‘MAX’ else cast(character_maximum_length as varchar) end +’)’,”) end + ‘ ‘ +
case when exists (
select id from syscolumns
where object_name(id)=so.name
and name=column_name
and columnproperty(id,name,’IsIdentity’) = 1
) then
‘IDENTITY(‘ +
cast(ident_seed(so.name) as varchar) + ‘,’ +
cast(ident_incr(so.name) as varchar) + ‘)’
else ”
end + ‘ ‘ +
(case when IS_NULLABLE = ‘No’ then ‘NOT ‘ else ” end ) + ‘NULL ‘ +
case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN ‘DEFAULT ‘+ information_schema.columns.COLUMN_DEFAULT ELSE ” END + ‘, ‘
from information_schema.columns where table_name = so.name
order by ordinal_position
FOR XML PATH(”)) o (list)
left join
information_schema.table_constraints tc
on tc.Table_name = so.Name
AND tc.Constraint_Type = ‘PRIMARY KEY’
cross apply
(select ‘[‘ + Column_Name + ‘], ‘
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH(”)) j (list)
where xtype = ‘U’
AND name NOT IN (‘dtproperties’)
Thanks Jacob
Now my problem is solved.
@Jignesh,
You are welcome! Glad to know it helped.
As Ponnu said, this can be achieved with the Publishing Wizard. But I’m guessing that this offers a higher level of granular control? :-)
Too bad you can’t use the wizard to configure the table query to use a “where filter” or a “columns to include filter” like DB Artisan. ;-)