Importing to SQL Express using SSMSE

When I used Enterprise Manager it was very easy to import required tables from another server into my local MSDE server.

Now I am using SQL Express and SSMSE I cannot seem to find any other way of importing data other than creating the insert scripts manually which is a very painful and tediuos operation!

Can anyone advise if I have missed something and there is a way to import easily using SSMSE?

Thanks.......in hope

hi,

SSMSE does not provide the wizards included in SSIS or the like, so you have to do it "your way"...

this usually means BCP data in, use INSERT INTO scripts, connect to linked servers (ifa available) and INSERT..SELECT data in...

regards|||

Bums!

Oh well at least I know now.....

Thanks for the info ;-)

|||

I cannot seem to find any other way of importing data

You can import data (but not table definitions) using the DTSWizard. It is a simplied version of the Import/Export Wizard from Enterprise Manager.

It is located in: {installdirectory}\Microsoft SQL Server\90\DTS\Binn\DTSWizrd.exe

|||When I look for the DTS Wizard in the location you specify, it isn't there!!

Man, it's frustrating not to have it...
|||

Download from here:

DTSWizard.exe
http://go.microsoft.com/fwlink/?LinkId=65111

Importing to SQL Express using SSMSE

When I used Enterprise Manager it was very easy to import required tables from another server into my local MSDE server.

Now I am using SQL Express and SSMSE I cannot seem to find any other way of importing data other than creating the insert scripts manually which is a very painful and tediuos operation!

Can anyone advise if I have missed something and there is a way to import easily using SSMSE?

Thanks.......in hope

hi,

SSMSE does not provide the wizards included in SSIS or the like, so you have to do it "your way"...

this usually means BCP data in, use INSERT INTO scripts, connect to linked servers (ifa available) and INSERT..SELECT data in...

regards|||

Bums!

Oh well at least I know now.....

Thanks for the info ;-)

|||

I cannot seem to find any other way of importing data

You can import data (but not table definitions) using the DTSWizard. It is a simplied version of the Import/Export Wizard from Enterprise Manager.

It is located in: {installdirectory}\Microsoft SQL Server\90\DTS\Binn\DTSWizrd.exe

|||When I look for the DTS Wizard in the location you specify, it isn't there!!

Man, it's frustrating not to have it...
|||

Download from here:

DTSWizard.exe
http://go.microsoft.com/fwlink/?LinkId=65111

sql

Importing tables

I am trying to import tables in an Access *.ADP file or using the Enterprise
Manager inteface.
My problem is that all the tables that I import (logging in as DBO) are lock
ed and I cannot update, delete or insert on them. Instead, all of the tables
wich I created using Access's *.ADP files or under the Enterprise Manager c
an be updated, have deleted
or inserted lines.
How can I solve it?What do you want to solve?
you want the tables in SQL server read-only as well?

>--Original Message--
>I am trying to import tables in an Access *.ADP file or
using the Enterprise Manager inteface.
>My problem is that all the tables that I import (logging
in as DBO) are locked and I cannot update, delete or
insert on them. Instead, all of the tables wich I created
using Access's *.ADP files or under the Enterprise
Manager can be updated, have deleted or inserted lines.
>How can I solve it?
>.
>

Importing or attaching a database

I had to reinstall sql7 and I now need to access my old
database. It is in the mssql7 data directory, but it does
not show up under enterprise manager. Is there a way
to "import" it?
Thanks for any help
Try using sp_attach_db. See SQL Server Books Online for more information on
this procedure, and examples.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"GB" <anonymous@.discussions.microsoft.com> wrote in message
news:2aa3f01c465ec$74153290$a301280a@.phx.gbl...
> I had to reinstall sql7 and I now need to access my old
> database. It is in the mssql7 data directory, but it does
> not show up under enterprise manager. Is there a way
> to "import" it?
> Thanks for any help

Importing or attaching a database

I had to reinstall sql7 and I now need to access my old
database. It is in the mssql7 data directory, but it does
not show up under enterprise manager. Is there a way
to "import" it?
Thanks for any helpTry using sp_attach_db. See SQL Server Books Online for more information on
this procedure, and examples.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"GB" <anonymous@.discussions.microsoft.com> wrote in message
news:2aa3f01c465ec$74153290$a301280a@.phx.gbl...
> I had to reinstall sql7 and I now need to access my old
> database. It is in the mssql7 data directory, but it does
> not show up under enterprise manager. Is there a way
> to "import" it?
> Thanks for any help

Importing or attaching a database

I had to reinstall sql7 and I now need to access my old
database. It is in the mssql7 data directory, but it does
not show up under enterprise manager. Is there a way
to "import" it?
Thanks for any helpTry using sp_attach_db. See SQL Server Books Online for more information on
this procedure, and examples.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"GB" <anonymous@.discussions.microsoft.com> wrote in message
news:2aa3f01c465ec$74153290$a301280a@.phx
.gbl...
> I had to reinstall sql7 and I now need to access my old
> database. It is in the mssql7 data directory, but it does
> not show up under enterprise manager. Is there a way
> to "import" it?
> Thanks for any help

Importing an Excel Spreadsheet into SQL Server

Hi,
I'm trying to import an excel file into SQL sever(using an insert statement), i'm creating a DTS package (in enterprise manager) and have VB Script. When i parse it, i get no errors, but when i run the package it says that it ran successfully but nothing happens, it doesnt insert into the table, even though i tested the insert statement. Can anyone help me?? Here's the code:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
on error resume next
Set objxl = CreateObject("Excel.Application")
objxl.Visible = False


Dim xlFile
xlFile = "C:\Data\file.xls"
Set objWkb = objxl.Workbooks.Open(xlFile)

'' Connecting to SQL Server
set cn = server.CreateObject("ADODB.Connection")

Dim serverName
serverName = "myserver2"


strCS = "Provider=SQLOLEDB; Data Source=myserver2;Initial
Catalog=mycat; Integrated Security=SSPI"

cn.ConnectionString = strCS
On Error Resume Next
cn.Open
Set objsht = objWkb.Worksheets.Open("Sheet1")
Dim client_name, rb, date_rvd, LOB
Dim sql
Dim row, sequence
row = 2

client_name = Trim(objsht.Cells(row, 2).Value)
Do While IsNull(client_name) = False And client_name <> ""
'client_name = Trim(objsht.Cells(row, 2))
rb = Trim(objsht.Cells(row, 4).value)
date_rvd = Trim(objsht.Cells(row, 6).value)
LOB = "WCS"

sql = "INSERT INTO TEMP_TEST (CLIENT_NAME, RB, DATE_REVIEWED, LOB) VALUES (" & _
" '" & client_name & "' ,'" & rb & "', " & date_rvd & ", '" & LOB & "');"


row = row + 1
MsgBox (sql)
client_name = Trim(objsht.Cells(row, 2).value)
cn.Execute (sql)

Loop

if err.count = 0 then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
End FunctionYou might want to refer to this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Please can you post the value of the sql variable? To us this is several magnitudes more important than all that vb.|||Why exactly are you using this script? It appears to be a very inneficient method to import the data (you're doing it line-by-line!!).|||Actually - I have read the code now. I would dump the on error resume next when debugging. Probably in production too but especially now.

Also - the loop is unecessary - refer to the link I sent. SQL Server is perfectly capable of dealing with an excel sheet as a table rather than a row at a time. Quicker and easier. As such you can skip all the ActiveX stuff altogether. DTS & ActiveX -> difficult to debug code. If possible try to do it all in T-SQL and run it from a job. You will find most of the guys here won't use ActiveX for very much if anything at all and work with the native T-SQL language.|||Why exactly are you using this script? It appears to be a very inneficient method to import the data (you're doing it line-by-line!!).Damn youngsters and their quick typing skills ;)|||Reminds me of some ActiveX in a DTS my old manager wrote...

It went through every row (46K) and assigned zeroes or blank strings to every NULL value... Needless to say it took aaaaaaaages.

A couple of very tiny T-SQL tweaks and it runns in seconds again ;) No prizes for guessing the function anyhow|||Thanks for the quick response, the sql variable = INSERT INTO TEMP_TEST (CLIENT_NAME, RB, DATE_REVIEWED, LOB) VALUES
( 'Adisseo USA Inc.' ,'Wesley Kent', 2/27/2007, 'WCS');

the reason i'm reading line by line is because i only want certain columns having certain values, it doesnt show that in this code, but eventually i will need to read line by line. Thanks!|||i only want certain columns having certain values
Can you expand on this please?
Rules/logic etc|||como se dice?|||Why not load all of it into a temporary table, extract what you want from it, and delete the temp table again?|||the rule is, read specific columns(not all), and then insert the values in a temp table. there are four tabs that need to be imported, all with different columns to import. but it needs to be in a DTS package. I guess loading it all would be another option|||Why not load all of it into a temporary table, extract what you want from it, and delete the temp table again -- With a DTS package?|||I take it you did not bother looking at the link then? You can specify columns & where clauses using that technique.|||Gotta ask - what is everyone's obsession with using DTS packages? Why stipulate that the answer, whatever it is, must be in a DTS package?

In any event - you can call T-SQL from a DTS package too.|||i got it, the problem was with the excel object "Trim(objsht.Cells(row, 2).Value)"
instead i used Trim(objxl.Cells(row, 2).Value). Thanks for all your suggestion guys!|||i did look at the link pootle, but in some cases i would need to skip lines, and i didnt know how to do that with that link|||Gotta ask - what is everyone's obsession with using DTS packages? ...

ummm... because I haven't learned the other way(s?) yet.
Besides, the graphical interface makes it so easy to follow job flow:

Importing a Text File

Hi,
I'm trying to parse a text file in SQL Server but am having problems
importing the file.
When I try to import the text file using Enterprise Manager it doesn't
preserve the row order. Currently I'm importing the text file into Access
(which preserves the order) and creating a primary key there. Then I can
import it into SQL Server and order by the key. Is there a way to skip this
ACCESS step and directly import into SQL server with the order intact?
Thank you very much.
Use ORDER BY when selecting the data.
TIA,
ChrisR
"Irving" wrote:

> Hi,
> I'm trying to parse a text file in SQL Server but am having problems
> importing the file.
> When I try to import the text file using Enterprise Manager it doesn't
> preserve the row order. Currently I'm importing the text file into Access
> (which preserves the order) and creating a primary key there. Then I can
> import it into SQL Server and order by the key. Is there a way to skip this
> ACCESS step and directly import into SQL server with the order intact?
> Thank you very much.
|||Thank you for responding.
The problem is there's nothing to order by. That's why I import into Access
first to create a primary key.
"ChrisR" wrote:
[vbcol=seagreen]
> Use ORDER BY when selecting the data.
> --
> TIA,
> ChrisR
>
> "Irving" wrote:
|||Might try using DTS to import the file into a table that contains a PK with
the IDENITY property. The SELECT ...ORDER BY PK column.
HTH
Jerry
"Irving" <Irving@.discussions.microsoft.com> wrote in message
news:AFD2116A-3615-4680-966C-8DCC17588C09@.microsoft.com...[vbcol=seagreen]
> Thank you for responding.
> The problem is there's nothing to order by. That's why I import into
> Access
> first to create a primary key.
> "ChrisR" wrote:
|||Irving,
One possibility is to add line numbers with
C:\> find /V /N "" c:\data\yourfile.txt > c:\data\yourfile2.txt
or write a short program in C or C++ or C# to do something similar.
(Or use a Unix shell.)
This DOS find command will add bracketed line numbers at the beginning
of each line, along with a blank line and a line with the file name at
the top, these first two without any line numbers. You could then
import this file into a table with one varchar(270) column and retrieve
both the line number and the contents with another query:
select
cast(substring(onlyColumn,2,charindex(']',onlyColumn)-1) as int) as
linenumber,
substring(onlyColumn, charindex(']',onlyColumn)+1,8000) as linecontent
from importedTable
where substring(onlyColumn,1,1) = '['
-- Steve Kass
-- Drew University
-- Ref: E434E144-6F3D-4A3A-9F00-4642ED84B33F
Irving wrote:

>Hi,
>I'm trying to parse a text file in SQL Server but am having problems
>importing the file.
>When I try to import the text file using Enterprise Manager it doesn't
>preserve the row order. Currently I'm importing the text file into Access
>(which preserves the order) and creating a primary key there. Then I can
>import it into SQL Server and order by the key. Is there a way to skip this
>ACCESS step and directly import into SQL server with the order intact?
>Thank you very much.
>
|||Thank you very much Steve.
It looks like this will work.
"Steve Kass" wrote:

> Irving,
>
> One possibility is to add line numbers with
>
> C:\> find /V /N "" c:\data\yourfile.txt > c:\data\yourfile2.txt
> or write a short program in C or C++ or C# to do something similar.
> (Or use a Unix shell.)
> This DOS find command will add bracketed line numbers at the beginning
> of each line, along with a blank line and a line with the file name at
> the top, these first two without any line numbers. You could then
> import this file into a table with one varchar(270) column and retrieve
> both the line number and the contents with another query:
> select
> cast(substring(onlyColumn,2,charindex(']',onlyColumn)-1) as int) as
> linenumber,
> substring(onlyColumn, charindex(']',onlyColumn)+1,8000) as linecontent
> from importedTable
> where substring(onlyColumn,1,1) = '['
>
> -- Steve Kass
> -- Drew University
> -- Ref: E434E144-6F3D-4A3A-9F00-4642ED84B33F

Importing a Text File

Hi,
I'm trying to parse a text file in SQL Server but am having problems
importing the file.
When I try to import the text file using Enterprise Manager it doesn't
preserve the row order. Currently I'm importing the text file into Access
(which preserves the order) and creating a primary key there. Then I can
import it into SQL Server and order by the key. Is there a way to skip this
ACCESS step and directly import into SQL server with the order intact?
Thank you very much.Use ORDER BY when selecting the data.
--
TIA,
ChrisR
"Irving" wrote:

> Hi,
> I'm trying to parse a text file in SQL Server but am having problems
> importing the file.
> When I try to import the text file using Enterprise Manager it doesn't
> preserve the row order. Currently I'm importing the text file into Access
> (which preserves the order) and creating a primary key there. Then I can
> import it into SQL Server and order by the key. Is there a way to skip th
is
> ACCESS step and directly import into SQL server with the order intact?
> Thank you very much.|||Thank you for responding.
The problem is there's nothing to order by. That's why I import into Access
first to create a primary key.
"ChrisR" wrote:
[vbcol=seagreen]
> Use ORDER BY when selecting the data.
> --
> TIA,
> ChrisR
>
> "Irving" wrote:
>|||Might try using DTS to import the file into a table that contains a PK with
the IDENITY property. The SELECT ...ORDER BY PK column.
HTH
Jerry
"Irving" <Irving@.discussions.microsoft.com> wrote in message
news:AFD2116A-3615-4680-966C-8DCC17588C09@.microsoft.com...[vbcol=seagreen]
> Thank you for responding.
> The problem is there's nothing to order by. That's why I import into
> Access
> first to create a primary key.
> "ChrisR" wrote:
>|||Irving,
One possibility is to add line numbers with
C:\> find /V /N "" c:\data\yourfile.txt > c:\data\yourfile2.txt
or write a short program in C or C++ or C# to do something similar.
(Or use a Unix shell.)
This DOS find command will add bracketed line numbers at the beginning
of each line, along with a blank line and a line with the file name at
the top, these first two without any line numbers. You could then
import this file into a table with one varchar(270) column and retrieve
both the line number and the contents with another query:
select
cast(substring(onlyColumn,2,charindex(']
',onlyColumn)-1) as int) as
linenumber,
substring(onlyColumn, charindex(']',onlyColumn)+1,8000) as linecontent
from importedTable
where substring(onlyColumn,1,1) = '['
-- Steve Kass
-- Drew University
-- Ref: E434E144-6F3D-4A3A-9F00-4642ED84B33F
Irving wrote:

>Hi,
>I'm trying to parse a text file in SQL Server but am having problems
>importing the file.
>When I try to import the text file using Enterprise Manager it doesn't
>preserve the row order. Currently I'm importing the text file into Access
>(which preserves the order) and creating a primary key there. Then I can
>import it into SQL Server and order by the key. Is there a way to skip thi
s
>ACCESS step and directly import into SQL server with the order intact?
>Thank you very much.
>|||Thank you very much Steve.
It looks like this will work.
"Steve Kass" wrote:

> Irving,
>
> One possibility is to add line numbers with
>
> C:\> find /V /N "" c:\data\yourfile.txt > c:\data\yourfile2.txt
> or write a short program in C or C++ or C# to do something similar.
> (Or use a Unix shell.)
> This DOS find command will add bracketed line numbers at the beginning
> of each line, along with a blank line and a line with the file name at
> the top, these first two without any line numbers. You could then
> import this file into a table with one varchar(270) column and retrieve
> both the line number and the contents with another query:
> select
> cast(substring(onlyColumn,2,charindex(']
',onlyColumn)-1) as int) as
> linenumber,
> substring(onlyColumn, charindex(']',onlyColumn)+1,8000) as linecontent
> from importedTable
> where substring(onlyColumn,1,1) = '['
>
> -- Steve Kass
> -- Drew University
> -- Ref: E434E144-6F3D-4A3A-9F00-4642ED84B33F

Importing a Text File

Hi,
I'm trying to parse a text file in SQL Server but am having problems
importing the file.
When I try to import the text file using Enterprise Manager it doesn't
preserve the row order. Currently I'm importing the text file into Access
(which preserves the order) and creating a primary key there. Then I can
import it into SQL Server and order by the key. Is there a way to skip this
ACCESS step and directly import into SQL server with the order intact?
Thank you very much.Use ORDER BY when selecting the data.
--
TIA,
ChrisR
"Irving" wrote:
> Hi,
> I'm trying to parse a text file in SQL Server but am having problems
> importing the file.
> When I try to import the text file using Enterprise Manager it doesn't
> preserve the row order. Currently I'm importing the text file into Access
> (which preserves the order) and creating a primary key there. Then I can
> import it into SQL Server and order by the key. Is there a way to skip this
> ACCESS step and directly import into SQL server with the order intact?
> Thank you very much.|||Thank you for responding.
The problem is there's nothing to order by. That's why I import into Access
first to create a primary key.
"ChrisR" wrote:
> Use ORDER BY when selecting the data.
> --
> TIA,
> ChrisR
>
> "Irving" wrote:
> > Hi,
> >
> > I'm trying to parse a text file in SQL Server but am having problems
> > importing the file.
> > When I try to import the text file using Enterprise Manager it doesn't
> > preserve the row order. Currently I'm importing the text file into Access
> > (which preserves the order) and creating a primary key there. Then I can
> > import it into SQL Server and order by the key. Is there a way to skip this
> > ACCESS step and directly import into SQL server with the order intact?
> >
> > Thank you very much.|||Might try using DTS to import the file into a table that contains a PK with
the IDENITY property. The SELECT ...ORDER BY PK column.
HTH
Jerry
"Irving" <Irving@.discussions.microsoft.com> wrote in message
news:AFD2116A-3615-4680-966C-8DCC17588C09@.microsoft.com...
> Thank you for responding.
> The problem is there's nothing to order by. That's why I import into
> Access
> first to create a primary key.
> "ChrisR" wrote:
>> Use ORDER BY when selecting the data.
>> --
>> TIA,
>> ChrisR
>>
>> "Irving" wrote:
>> > Hi,
>> >
>> > I'm trying to parse a text file in SQL Server but am having problems
>> > importing the file.
>> > When I try to import the text file using Enterprise Manager it doesn't
>> > preserve the row order. Currently I'm importing the text file into
>> > Access
>> > (which preserves the order) and creating a primary key there. Then I
>> > can
>> > import it into SQL Server and order by the key. Is there a way to skip
>> > this
>> > ACCESS step and directly import into SQL server with the order intact?
>> >
>> > Thank you very much.|||Irving,
One possibility is to add line numbers with
C:\> find /V /N "" c:\data\yourfile.txt > c:\data\yourfile2.txt
or write a short program in C or C++ or C# to do something similar.
(Or use a Unix shell.)
This DOS find command will add bracketed line numbers at the beginning
of each line, along with a blank line and a line with the file name at
the top, these first two without any line numbers. You could then
import this file into a table with one varchar(270) column and retrieve
both the line number and the contents with another query:
select
cast(substring(onlyColumn,2,charindex(']',onlyColumn)-1) as int) as
linenumber,
substring(onlyColumn, charindex(']',onlyColumn)+1,8000) as linecontent
from importedTable
where substring(onlyColumn,1,1) = '['
-- Steve Kass
-- Drew University
-- Ref: E434E144-6F3D-4A3A-9F00-4642ED84B33F
Irving wrote:
>Hi,
>I'm trying to parse a text file in SQL Server but am having problems
>importing the file.
>When I try to import the text file using Enterprise Manager it doesn't
>preserve the row order. Currently I'm importing the text file into Access
>(which preserves the order) and creating a primary key there. Then I can
>import it into SQL Server and order by the key. Is there a way to skip this
>ACCESS step and directly import into SQL server with the order intact?
>Thank you very much.
>|||Thank you very much Steve.
It looks like this will work.
"Steve Kass" wrote:
> Irving,
>
> One possibility is to add line numbers with
>
> C:\> find /V /N "" c:\data\yourfile.txt > c:\data\yourfile2.txt
> or write a short program in C or C++ or C# to do something similar.
> (Or use a Unix shell.)
> This DOS find command will add bracketed line numbers at the beginning
> of each line, along with a blank line and a line with the file name at
> the top, these first two without any line numbers. You could then
> import this file into a table with one varchar(270) column and retrieve
> both the line number and the contents with another query:
> select
> cast(substring(onlyColumn,2,charindex(']',onlyColumn)-1) as int) as
> linenumber,
> substring(onlyColumn, charindex(']',onlyColumn)+1,8000) as linecontent
> from importedTable
> where substring(onlyColumn,1,1) = '['
>
> -- Steve Kass
> -- Drew University
> -- Ref: E434E144-6F3D-4A3A-9F00-4642ED84B33F