script out data? - 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 Development (2000)

script out data?

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2003-06-10 : 14:49:58

Hi,
I'm writing an application which uses data inorder perform a number of calculations. This calcuation data changes fairly often. The application I'm writing will be used by clients who are running the database on their independant system. Is there anyway I can script out data for individual tables so the client can update thier database by running a simple sql script (with lots of insert statements etc.)? I've looked at DTS but since the database will not be inhouse it could be difficult to have the client admin set up the DTS utility. There is no connection or link between the two databases.

I just need an easy way to take the data from one database and send(email/burn a CD) the data to the client who can then update their system. Most likely the client will not have that much sql knowledge so the easier the better.

Thanks

(I've thought about sprocs that open an access database and insert the data but it would be nice if the files were just plain text)

Nic

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-06-10 : 14:54:43

Have you looked at bcp?
Also take a look at http://www.lockwoodtech.com/auto_inserts.htm

Owais

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-10 : 15:07:55

Here's a quick example of bcp (command line utility):

bcp DB1.dbo.Table1 out C:\temp\Table1.txt -c -Sserver1 -Uuser1 -Puser1pwd -r\r\n

The above example exports the data from Table1 into a text file.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-10 : 15:10:14

You could use bcp and send them a bat file to run it.

I have an SP which creates insert statements from a table.
Create Procedure sp_CreateDataLoadScript
@TblName varchar(128)
as
/*
sp_CreateDataLoadScript 'DatabaseBackup'
*/
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 when DATA_TYPE like '%date%' then 2 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 + ' + '''''''''
when ColType = 2 then ''''''''' + convert(char(9),' + ColName + ',112) + convert(char(12),' + ColName+ ',114) + '''''''''
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.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chadmat
The Chadinator

1974 Posts

Posted - 2003-06-10 : 22:38:03

Try the free utility at [url]www.clrsoft.com[/url]

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources