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
