Export DTS

I have a SQL database on a local server that I need to recieve data from a external database. Both machines are SQL 2000 the local version just needs data from the web version. The local version has a few different tables but besides that pretty much the same.

I want it to export data from the web version daily and just add whats new. I tryed it and it just doubled the data I had it drop the tables and create new ones but it did not do that.

Is there a good how-to on the web or somewhere?

Thanks for your helpThe simplest way to do it is to truncate (NOT drop) the table on the local machine before you import the full set of data. Add an 'Execute SQL' task to your package that has a SQL statement of 'Truncate Table xyz'.

Once that is complete, continue as before and import the data.

If you want to keep the exisiting data in the local table and only add new data from your web database server, then you would probably need to set up a linked server between the 2. The linked server would enable you to only import rows that are not in the destination server (by specifiying the rows to add in a SQL statement in the 'Source' section of your Transform task).

If your Web server db contains all the data you need, then the truncate option is probably the easier way to go. However, it does mean that your local table will not have data for couple of seconds/minutes (between the truncate and the end of the data pump).

Lionel|||Why dont use DTS package to transfer data between the tables and add a Data Driven Query Task in order to append only the required records

Balaji

Export Data from Tables to Files Using Procedures

Hi

Having small query

How can i able to Export Data from Tables to Specific Local files using Procedures?

Environment Details

OS : Windows 2000 server
Database : MS-SQL Server

Solutions are needed asap.

Thanks in advance

cheers
vasuTake a look at bcp/osql in sql book online.

e.g.

declare @.sql nvarchar(1000)

set @.sql='bcp "Northwind..Orders" out "c:\Orders.txt" -w -T -S"' + @.@.servername + '"'

exec master..xp_cmdshell @.sql

exporing a table from one dattabse to another

I have a database which is on a network and I want to transfer a table form this databse onto my local machine. Is this possible as in Access they have an export function but I can't find such a thing on sql server expresss.

I have tried copying and pasting the data across but there are over 200,000 rows so I imagine it will take me for ever.

As anyone any suggestions?

Cheers

hi,

SSMSE does not provide SSIS (SQL Server Integration Service) features to allow a task like that, but you can perform the very same activity via standard Transact-SQL code...

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.originalTB (

Id int NOT NULL PRIMARY KEY,

data varchar(10) NULL

);

GO
PRINT 'populate it with some data';

DECLARE @.i int, @.data varchar(10);

SET @.i = 1;

WHILE @.i < 11 BEGIN

IF @.i % 5 = 0

SET @.data = NULL;

ELSE

SET @.data = RIGHT(REPLICATE('0', 9) +CONVERT(varchar, @.i), 10);

INSERT INTO dbo.originalTB VALUES ( @.i, @.data);

SET @.i = @.i + 1

END;

GO

--SELECT * FROM dbo.originalTB;

GO

PRINT 'Create the destination table on the fly using SELECT INTO statement';
PRINT 'see http://msdn2.microsoft.com/en-us/library/ms189499.aspx as well';

PRINT 'you have to later add all required constraints (check, default, primary key)';

PRINT 'on the destination table as the SELECT INTO does not perform it..'
PRINT 'see SSMSE or further tools like my amScript, available for free.';

SELECT *

INTO dbo.destinationTB

FROM dbo.originalTB

ORDER BY Id;

SELECT * FROM dbo.destinationTB;

GO

DROP TABLE dbo.destinationTB;

GO

PRINT 'Explicitely create the destination table, again, see the "Scripting features"';

PRINT 'of SSMSE or further tools like my amScript, available for free.';

PRINT 'and populate it via a "standard" INSERT SELECT';

CREATE TABLE dbo.destinationTB (

Id int NOT NULL PRIMARY KEY,

data varchar(10) NULL

);

INSERT INTO dbo.destinationTB

SELECT Id, data

FROM dbo.originalTB

ORDER BY Id;

SELECT * FROM dbo.destinationTB;

GO

DROP TABLE dbo.originalTB, dbo.destinationTB;

--<-

Create the destination table on the fly using SELECT INTO statement

you have to later add all required constraints (check, default, primary key)

on the destination table as the SELECT INTO does not perform it..

Id data

-- -

1 0000000001

2 0000000002

3 0000000003

4 0000000004

5 NULL

6 0000000006

7 0000000007

8 0000000008

9 0000000009

10 NULL

Explicitely create the destination table, see the "Scripting feature"

of SSMSE or further tools like my amScript, available for free at

http://www.asql.biz/en/Download2005.aspx

and populate it via a "standard" INSERT SELECT

Id data

-- -

1 0000000001

2 0000000002

3 0000000003

4 0000000004

5 NULL

6 0000000006

7 0000000007

8 0000000008

9 0000000009

10 NULL

regards