Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> SQL Server >> SQL Server Tools General

Generate SQL for a table

I have two questions

i) is there any SP which will generates SQL Script for a table including all constarint(PK,FK), index etc. and the table data in the format of "INSERT INTO" . if NO, how can i create to do so, or any other SP which will help me( may be third party one)

ii)Where had "Scptxfr.exe" gone in SQL Server 2005 ?

Regards,

Thanks.

Gurpreet S. Gill

[1123 byte] By [GurpreetSinghGill] at [2007-12-25]

«« TFS errors after enabling SP1 ISAPI filter
»» I want to know some good website for software architecture.

# 1

There is no SP. You will have to create one yourself or use SMO for example. I don't know the answer to the 2nd one. I am moving the thread to Tools forum to see if someone there might know.

UmachandarJayachandran-MS at 2007-9-3 >

top of Msdn Tech,SQL Server,SQL Server Tools General...

# 2

Gurpreet Singh Gill wrote:

Hi
I have two questions
i) is there any SP which will generates SQL Script for a table including all constarint(PK,FK), index etc. and the table data in the format of "INSERT INTO" . if NO, how can i create to do so, or any other SP which will help me( may be third party one)
ii)Where had "Scptxfr.exe" gone in SQL Server 2005 ?
Regards,
Thanks.
Gurpreet S. Gill

Hi,

if you are a .Net developer the insert scripts are already genrated for you by the wizard if you have pk in every tables. You might be interested in using it. even if you are not a VS developer
the answer to the second question is just a click away. here. check this out
look for this topic Where to Find SCPTXFR?
http://www.sqlservercentral.com/columnists/jreade/howtoscheduleasqlserverdatabasecreationscript.asp

thanks,

joey

joeydj at 2007-9-3 >

# 3

joey

I dont want to create it by .NET code infact only by using the some SP, or inside SSMS only.

I read the artical but i dont find any thing about where is the SCPTXFR.EXE had gone, only one line say, "this is missing", but they are refering the Beta version of SQL Server.

Regards,

Thanks.

Gurpreet S. Gill

GurpreetSinghGill at 2007-9-3 >

# 4

the title is misleading. sorry

anyway you can still use the v2000 version of scptxfr.exe

with 2005

joeydj at 2007-9-3 >

# 5

I know about scptxfr.exe, I want only one table Script(not all), with Data too.

GurpreetSinghGill at 2007-9-3 >

# 6

hi,

got this one from another thread
you can use the sql server wizard to generate scripts insert, update, delete query
Run sql server enterprise manager > tools > wizards > database > create stored procedures wizards.
choose the table and click on insert.

on the completing tab click on edit then click edit Sql

regards,

joey

joeydj at 2007-9-3 >

# 7

Sorry to say man, i want to create the SP which will Generate the SQL Script(including Constaints, indexes, checks..etc) for the table as well as the data of that table. The aim is to create the .sql file, which can be used for migration to other DB with the table(create script) with data.

GurpreetSinghGill at 2007-9-3 >

# 8

I was struggling with the same case a few months back in SQL Server 2005 as the wizard does not include drop statements before creating the object. Unlike SQL Server 2000, the 2005 version uses the IF NOT EXISTS logic instead of the IF EXISTS logic. This is where chanced upon Scriptio, a tool created by SQLTeam.com. From the looks of your requirement, you definitely have to write a script (or an application) to generate an INSERT Statement if you want to migrate the data as well. Why don't you just generate a script to build the database and use SSIS to migrate the data afterwards?

bass_player at 2007-9-3 >

# 9

Basically, the things are bit different for me, we had a setup at client end, which uses the SQL Server 2005 as a backend(before that the application was using the SQL Base by Centura/Gurpta Technologies).Client dont have SSMS(only Database Services), infact they are using the SQL Talk(again the tool from the same). they want the same funcrionalties as provided by SQL Base, this procedure, which i need is one of them

Could any body help me.

Regards,

Thanks.

Gurpreet S. Gill

GurpreetSinghGill at 2007-9-3 >

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech