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.
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 |
|
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\nThe 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_POSITIONdeclare @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 #ago==========================================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]-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
|
|
|
|