Posting DDL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Posting DDL

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-31 : 18:04:08

I should have known this along time ago, but I havent figured it out yet. - How do I create scripts that create the table and insert the data into it as well ? This is just for small tables of less than a dozen rows and is for posting SQLTEAM forums purposes.

Its called DDL right ?

What does it stand for ?

thanks
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 18:17:58

DDL is the CREATE TABLE statements. DML would be the INSERT INTO statements. DML is data manipulation language and includes UPDATEs and DELETEs as well. To generate the DDL for your objects, highlight them in EM, right click, all tasks, generate SQL script. INSERT INTO statements is manual if you don't have a third party tool to do it for you or a script that can do it. For the INSERT INTO statements, we usually just need about 5-10 rows in the tables so it should be pretty quick to write these up.

Tara

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-08-31 : 18:34:30

quote:
Originally posted by tduggan

DDL is the CREATE TABLE statements. DML would be the INSERT INTO statements. DML is data manipulation language and includes UPDATEs and DELETEs as well. To generate the DDL for your objects, highlight them in EM, right click, all tasks, generate SQL script. INSERT INTO statements is manual if you don't have a third party tool to do it for you or a script that can do it. For the INSERT INTO statements, we usually just need about 5-10 rows in the tables so it should be pretty quick to write these up.

Tara

Hi Tara,

Do you recommend any scripts for the DML ? Any free 3rd party tools

thanks a bunch

mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 18:38:23

I wouldn't recommend purchasing anything for so little data. The tool that I use for lookup tables is Red Gate's SQL Data Compare. But that certainly isn't worth the price if you only need to write out a few commands.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-31 : 20:18:41

Hi Mike,

To script the table use QA or EM.

Now I will show you a "trick" to script sample insert statements.
Hopefully it is readable

To generate insert statements... ( northwind..products sample )
n.b. just for the "normal" datatypes

1. run this for the table you want to script....

select 'isnull(quotename('+column_name+',''''''''),''''),'
from information_schema.columns
where table_name = 'Products'

2. with the results of (1) do something like this:

select
	'insert Products(...) values('+
	isnull(quotename(ProductID,''''),''),
	isnull(quotename(ProductName,''''),''),
	isnull(quotename(SupplierID,''''),''),
	isnull(quotename(CategoryID,''''),''),
	isnull(quotename(QuantityPerUnit,''''),''),
	--isnull(quotename(UnitPrice,''''),''),
	isnull(quotename(UnitsInStock,''''),''),
	isnull(quotename(UnitsOnOrder,''''),''),
	isnull(quotename(ReorderLevel,''''),''),
	isnull(quotename(Discontinued,''''),''),
	isnull(quotename(LastUpdate,''''),'')
	+ ')'
from Products

3. important: Set QA to output in Text with custom delimiter "," ( Options Dialog )
When you run (2) you will get something like:

insert Products(...) values('1','Modified product','1','1','10 boxes x 20 bags','39','0','10','0','Mar 16 2004 10:26PM')
insert Products(...) values('2','Chang','1','1','24 - 12 oz bottles','17','40','25','0','Jan 7 2004 7:40PM')
insert Products(...) values('3','Aniseed Syrup','1','2','12 - 550 ml bottles','13','70','25','0','Jan 7 2004 7:40PM')
insert Products(...) values('4','Chef Anton''s Cajun Seasoning','2','2','48 - 6 oz jars','53','0','0','0','Jan 7 2004 7:40PM')
insert Products(...) values('5','Chef Anton''s Gumbo Mix','2','2','36 boxes','0','0','0','1','Jan 7 2004 7:40PM')

4. run

select top 0 * from Products

5. use the results of (4) to get:

column list:
ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued,LastUpdate

.
.
.
.

insert Products(/*ProductID,*/ProductName,SupplierID,CategoryID,QuantityPerUnit,/*UnitPrice,*/UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued,LastUpdate)
values(/*'1',*/'Modified product','1','1','10 boxes x 20 bags','39','0','10','0','Mar 16 2004 10:26PM')

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-08-31 : 20:39:27

or you could use Vyas' approach

http://vyaskn.tripod.com/code.htm#inserts

-ec

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-31 : 20:44:29

Yeah I was going to post that link. The reason I didn't is because it will generate the insert statements for every row in a table. As long as you don't post all of it, then that's fine to use that tool. We only need a handful of rows typically to help you with the query.

Tara

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2004-09-10 : 17:46:58

quote:
Originally posted by tduggan

Yeah I was going to post that link. The reason I didn't is because it will generate the insert statements for every row in a table. As long as you don't post all of it, then that's fine to use that tool. We only need a handful of rows typically to help you with the query.

Tara

Hmm..no. There are two ways in which you can limit the output of my stored procedure sp_generate_inserts. Using the @Top parameter or @From parameter!

--
HTH,
Vyas
http://vyaskn.tripod.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-10 : 17:49:50

Oops! Forgot about that. Haven't had to use your script in a while.

Been busy Vyas? Haven't seen ya around much.

Tara

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2004-09-11 : 03:36:05

quote:
Originally posted by tduggan

Oops! Forgot about that. Haven't had to use your script in a while.

Been busy Vyas? Haven't seen ya around much.

Tara

Yeah, a bit busy with projects at work! But I check the forums once in a while to see anyone is talking about me ;-)

--
HTH,
Vyas
http://vyaskn.tripod.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources