scripting sql server data as well as objects

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

Import/Export (DTS) and Replication (2000)

scripting sql server data as well as objects

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-24 : 18:09:17

cathal writes "Hello,
I would like to know if there is any straightforward way to script sql server data along with object structures etc. I know I can script database objects (tables, stored procedures), and also bring along attributes such as permissions, constraints etc., but I would like to script the data as well e.g. a series of correct INSERT statements.

I can probably write my own tool to do this, but before I spend time on this, I would appreciate any pointers.

I am predominantly using sql 2000 sp2 in a w2k environment.

thanks"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-24 : 18:42:02

Hi

Writing your own tool is a fun excercise. Basically, you go through the system tables (sysobjects and syscolumns) to generate an insert statement, then go through the table and create some SQL for every row.

However, if you just want an off the shelf solution, go to www.lockwoodtech.com and get a copy of "Auto-Inserts". It will do everything you want.

Damian

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-24 : 20:30:47

from www.nigelrivett.com
Doesn't guarantee to cope with all cases but should be easy to change if you find anything it fails on.

Create Procedure sp_CreateDataLoadScript
@TblName varchar(128)
as

select @TblName = db_name() + '..' + @TblName

create table #a (id int identity (1,1), ColType int, ColName varchar(128))

insert #a (ColType, ColName)
select case when DATA_TYPE like '%char%' then 1 else 0 end ,
COLUMN_NAME
from information_schema.columns
where TABLE_NAME = @TblName
order by ORDINAL_POSITION

declare @id int ,
@maxid int ,
@cmd1 varchar(7000) ,
@cmd2 varchar(7000)

select @id = 0 ,
@maxid = max(id)
from #a

select @cmd1 = 'select '' insert ' + @TblName + ' ( '
select @cmd2 = ' + '' select '' + '
while @id < @maxid
begin
select @id = min(id) from #a where id > @id

select @cmd1 = @cmd1 + ColName + ','
from #a
where id = @id

select @cmd2 = @cmd2
+ ' case when ' + ColName + ' is null '
+ ' then ''null'' '
+ ' else '
+ case when ColType = 1 then ''''''''' + ' + ColName + ' + ''''''''' else ColName end
+ ' end + '','' + '
from #a
where id = @id
end

select @cmd1 = left(@cmd1,len(@cmd1)-1) + ' ) '' '
select @cmd2 = left(@cmd2,len(@cmd2)-8) + ' from ' + @tblName

select '/*' + @cmd1 + @cmd2 + '*/'

exec (@cmd1 + @cmd2)
drop table #a

go

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-24 : 23:28:29

Now here's a challenge - how about your own tool that also takes into account your object dependencies - ie if an SP uses a function - then you'll need to run the script for the function first - etc etc etc

C'mon nigel - what do we do?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-25 : 06:50:58

Run the script that creates all the functions first?

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-25 : 17:15:20

and then what about the stored procedures that depend on other stored procedures?, views that depend on views?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-25 : 20:09:57

Have a look at the release control article on my web site.
I am enhancing it for publication and adding to it the tables I use to generate the scripts - these include the order in which objects are created.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-25 : 20:16:05

nr - all I can say is "

"

oh and of course

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources