TO GENERATE SCRIPTSTRUCTURE WITH DATA

Question

TO GENERATE SCRIPTSTRUCTURE WITH DATA

patr

SSC Eights!

Points: 892
More actions
July 29, 2008 at 4:41 am

#125323

hi
I want to generate a script,should contains both the structure and the data existing in the database.Is ii available in sql server 2005
thanks in advance ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

For my knowledge there is no complete scripting available for what you want to do.

You can easily generate a script to create your tables and even the database (right click from SSMS). But there is no script to generate the contents of the table.

You could create something yourself with a little querying.

It should look like this (for a table with a numeric first column and a char second column):

select

'INSERT INTO Table (column1, column2) VALUES (' + Cast(column1 AS char(10)) + ', ''' + column2 ''')' AS InsertString

from [Table]

The code above creates all INSERT commands necassary to put the contents in the table. Export the results to a textfile and execute this on another server.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

But maybe it is more easy to use a backup-restore procedure to create and fill a database.

Can you clearify why you want/need to script it?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 3

Getting a backup of the existing and restoring is the best procedure to achieve your task.

Venkatesan Prabu .J

http://venkattechnicalblog.blogspot.com/

Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:

http://venkattechnicalblog.blogspot.com/

HanShi SSC-Dedicated Points: 33507 More actions · Answer 4

In addition to my first post in this topic:

Someone allready created a SP (SP_Generate_Inserts) to script the contents of a table to INSERT commands. Look at this url: http://vyaskn.tripod.com/code/generate_inserts_2005.txt

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

liebesiech SSCrazy Points: 2610 More actions · Answer 5

have a look at the SQL Tool belt from redgate as well. One of the tools in there is just doing this. There is a 30 day trial available.