Wednesday, March 21, 2012

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

No comments:

Post a Comment