Import script

Hello All,
I need import some data from one db in one server to another db in another
server. I can do it by import/export wizard, but I need do that by script
for deployment time. Do you know how to do that?
Thanks,
HUwhich database server are you using? 2000 or 2005|||2000
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1176215374.790446.12340@.p77g2000hsh.googlegroups.com...
> which database server are you using? 2000 or 2005
>|||Hello,
Vysa's have some good utility procedures to generate insert statements, its
all pretty handy.
http://vyaskn.tripod.com/code.htm#inserts
Thanks
Hari
"italic" <hugur@.hotmail.com> wrote in message
news:%23eFv013eHHA.4136@.TK2MSFTNGP02.phx.gbl...
> 2000
> "Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
> news:1176215374.790446.12340@.p77g2000hsh.googlegroups.com...
>

Import script

Hello All,
I need import some data from one db in one server to another db in another
server. I can do it by import/export wizard, but I need do that by script
for deployment time. Do you know how to do that?
Thanks,
HUwhich database server are you using? 2000 or 2005|||2000
"Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
news:1176215374.790446.12340@.p77g2000hsh.googlegroups.com...
> which database server are you using? 2000 or 2005
>|||Hello,
Vysa's have some good utility procedures to generate insert statements, its
all pretty handy.
http://vyaskn.tripod.com/code.htm#inserts
Thanks
Hari
"italic" <hugur@.hotmail.com> wrote in message
news:%23eFv013eHHA.4136@.TK2MSFTNGP02.phx.gbl...
> 2000
> "Ajit - The Scorpio" <ajitscorpio@.gmail.com> wrote in message
> news:1176215374.790446.12340@.p77g2000hsh.googlegroups.com...
>> which database server are you using? 2000 or 2005
>sql

Import index and constain

Dear
I export the database table,SP,view,use..etc to script
file. After i run the script to another server to rebuild
the database. Only the table(index and constain) can't
import to new database but i have choice the option(index
and constain) when i export the database to script. What
is ths problem?
Many Thanks
JohnJohn
How did you export your objects?
Try using DTS , there is an optino "Transfer Objects" so you will find that
you can export indexes and keys.
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:066301c3c476$0eb20a80$a501280a@.phx.gbl...
> Dear
> I export the database table,SP,view,use..etc to script
> file. After i run the script to another server to rebuild
> the database. Only the table(index and constain) can't
> import to new database but i have choice the option(index
> and constain) when i export the database to script. What
> is ths problem?
> Many Thanks
> John

Import csv file to MS SQL 2005

Hi Guys,

I have been trying to search for a free asp or asp.net script that will allow me to upload a .csv file and import it into an MS SQL Database. As its going to be a ProductCatalog and pricing changes nearly 2nd day. And wanting to an a script that I can put in my admin panel on my site to upload a .csv file and import it to a MS SQL Database.

I will be updating fields as well as adding new products. So the upload script would need to be able to handle those two things.

Is their any good free scripts around that people can recommend.

Thanks

Matthew

Which version of SQL server are you using?

SQL Server Integration Services will do this nicely...

|||

Using SQL 2005 Standard Edition, as the database will be used on a Website, don't want to have to keep logging into the control panel and then going and using the Web-Based SQL Management Tools.

Matthew

|||

Hello,

http://www.nigelrivett.net/ImportTextFiles.html is a script to import text files that arrive in a directory into a table.

It will process every file in the directory with the correct filemask and move the file to an archive directory on completion.
It can be used in conjunction with an ftpget SP to import files from a ftp server .

You could add some minor change to import csv file.

import ascii file with ssis and script

Hi, i've question about how to import an ascii-file in a sql 2005 table.
I want to import this file also with an unique key. There i first have to get the last key form the table and then raise this key. Next step is to use this key during the import.

How do i have to do this in ssis?
Thanks in advance

OlafNo problem.

While in your control flow, add a variable named MaxKey of integer type. Then in your control flow, right before the data flow task, add an Execute SQL task. Set its ResultSet to Single row. Setup the connection in it to point to your database and the for the SQLStatement, use: "select max(keyfield) from your_table". Click on the Result Set option on the left-hand side of the editor. Set the result name to 0 and chose User::MaxKey as the Variable Name. Click OK.

In your data flow right before the OLE destination, add a Script Component. Chose to use it as a transformation. Edit it. On the left, select "Inputs and Outputs". Expand Output 0 and then "Add Column" to the Output Columns folder and call it "NewKey. Make sure it is an integer data type big enough to hold a key big enough for your table. Then click on "Script" on the left to bring up the script parameters. Add "MaxKey" to the ReadOnlyVariables box. Then, click on the Design Script... button. Here's your script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Private NextKey As Int32 = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaximumKey As Int32 = Me.Variables.MaxKey ' Grab value of MaxKey which was passed in

' NextKey will always be zero when we start the package.
' This will set up the counter accordingly
If (NextKey = 0) Then
' Use MaximumKey +1 here because we already have data in the table, or we'll start with 0+1=1 if we don't
' and we need to start with the next available key
NextKey = MaximumKey + 1
Else
' Use NextKey +1 here because we are now relying on
' our counter within this script task.
NextKey = NextKey + 1
End If

Row.NewKey = NextKey ' Assign NextKey to our AdFormKey field on our data row
'
End Sub

End Class|||Phil,
Thansks for your answer, and i'm doing wel till step 'Script Component'. After i've clicked on 'script' and add 'MaxKey', i get an error message after i've clicked on button 'Design Script'. The message is: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container...' What do i wrong?
Thanks.

Olaf|||See the following post which explains the solution: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1

In short, save a copy of the SSIS variable (e.g. Me.Variables.Whatever ) in the OnPreExecute subroutine. Use the copy in lieu of Me.Variables.Whatever in you ProcessInputRow() subroutine.|||thanks jaegd, problem is solved. My i ask you an other question?
I get an error message 'Cannot create connector. the destination component does not hav any available inputs for use in creating a path' after i've connect the script component at the destination ole. What goes wrong? When i look to the properties of the OLE, i see some unuased input colummns.
Thanks in advance|||

jaegd wrote:

See the following post which explains the solution: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=956181&SiteID=1

In short, save a copy of the SSIS variable (e.g. Me.Variables.Whatever ) in the OnPreExecute subroutine. Use the copy in lieu of Me.Variables.Whatever in you ProcessInputRow() subroutine.

I don't think this is the problem. I detailed the steps exactly as I use it. Either the OP has a typo, or he didn't define the variable in the right scope. The variable must be scoped to the package. The solution above is too much work.|||

Olaf vd Sanden wrote:

Phil,
Thansks for your answer, and i'm doing wel till step 'Script Component'. After i've clicked on 'script' and add 'MaxKey', i get an error message after i've clicked on button 'Design Script'. The message is: 'The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container...' What do i wrong?
Thanks.

Olaf

You must define the variable when in the control flow. Make sure that the variable's scope is set to the package.

Then make sure you type the variable name in the ReadOnlyVariables section on the script parameters page, as I have indicated in my instructions. Do not include any extra spaces when typing this variable name in the ReadOnlyVariables box.|||

Olaf vd Sanden wrote:

thanks jaegd, problem is solved. My i ask you an other question?
I get an error message 'Cannot create connector. the destination component does not hav any available inputs for use in creating a path' after i've connect the script component at the destination ole. What goes wrong? When i look to the properties of the OLE, i see some unuased input colummns.
Thanks in advance

Please make sure you are using a destination OLE connector and not a source connector.|||Phil, First i had a data conversion before my destionation ole. After i've removed this is was possible to connect the script component. Sorry for my questions, it's new for me. But, isn't possible to use a data conversion and a script component?
Thanks, Olaf|||

Olaf vd Sanden wrote:

Phil, First i had a data conversion before my destionation ole. After i've removed this is was possible to connect the script component. Sorry for my questions, it's new for me. But, isn't possible to use a data conversion and a script component?
Thanks, Olaf

Sure it's possible. You must be sure that when you dropped in the script component that you selected "Transformation," not "Source" or "Destination."|||Phil, the script component propertie is 'Transformation', but still i can't connect a data covnversion and a script component add an Ole DB Destination
The situation that i want in the data flow is:
a 'Flate File source editor' > 'Data Conversion Tranformation editor' > 'OLE DB destination Editor' and also the 'Script Component' > 'OLE DB destination Editor'

Sorry again, but i'm new with this.
Thanks in advance.

Olaf|||

Olaf vd Sanden wrote:

Phil, the script component propertie is 'Transformation', but still i can't connect a data covnversion and a script component add an Ole DB Destination
The situation that i want in the data flow is:
a 'Flate File source editor' > 'Data Conversion Tranformation editor' > 'OLE DB destination Editor' and also the 'Script Component' > 'OLE DB destination Editor'

Sorry again, but i'm new with this.
Thanks in advance.

Olaf

Your OLE DB destination has to be at the END of the dataflow. You cannot have two in the same dataflow, unless you've split the dataflow with a multicast, conditional statement, etc...

Why do you have the OLE DB destination between the Data Conversion transformation and the Script component?|||Phil, Thanks for your reply. I think i've solved the problem. I've used an 'Union All' where i connected the 'Flate File source editor' and 'Script Component'. After the 'Union' comes a 'Data conversion' and then the 'OLE DB Destination'. I like to hear from you if this the right way.

Now i get an error message in the 'Execution Results': [DTS.Pipeline] Error: component "Union All" (14793) failed the pre-execute phase and returned error code 0x80070057.

What do I do wrong now?
Thanks.

Olaf|||Your flat file should hook into the data conversion transformation and then to the script component and then to the OLE DB destination.

FF -> Data Conversion -> Script Component -> OLE DB Destination.

No union is necessary.|||Phil, Thanks again for your fast reply. And it works.
Again thanks.
Olaf

import a CSV delimited text file into a table

Hi,

Could you help me to write a script to import a CSV delimited text file into a sql server table.?

Thanks,

carlos

Hi,

you can use either a linked server or OPENQUERY for ahhoc querying the data, or the Import wizard of Management Studio. A sample of this would be:

http://p2p.wrox.com/topic.asp?TOPIC_ID=20163

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks Jens, but it is too complex.

I am using BULK INSERT, but now I have a problem when a try to load a DATE value, I get error

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (EffectiveDate).

The script is

BULK INSERT grouppolicy

FROM 'C:\datatoload\test.txt'

WITH (

FIELDTERMINATOR = '\t',

ROWTERMINATOR = '\n'

)

Can Somebody help me

Thanks,

Import + ActiveX (cross)

What I will do is create a temporary table in the same
database with the data from the file.
Next, I will write a script to (import) insert from the
temp table to where you want the data to go.
Mary

>--Original Message--
>Hey,
>1)
>I'm doing a lot of importing (by DTS packages) from
commaseparated files
>into tables where i empty/truncate the table _before_
import of ALL info in
>the file.
>BUT - how do I import data from such a file by an UPDATE
command...
>Meaning if the table has an ID col and a NAME col, and
the file has an ID
>col and a NAME col - then I would like to be able to
UPDATE all NAME-cols in
>the table by using the info from the file. The NAME in
the file might have
>changed, but the ID stays the same... Also, i new ID's
are in the file, they
>should be INSERTed into the table (+ the NAME).
>2)
>I have been using ActiveX Data Trans for some imports -
but it would be
>great if I could perform SQL-taks (like above
perhaps...? or is the
>another way) - meaning: how do I make SQL call from
within an ActiveX task?
>
>Any help appreciated - Thanx!
>Best regards
>Jakob H. Heidelberg
>Denmark
>
>
>
>.
>Ah, allright - does somebody have code examples I can use?
Best regards
Jakob
"Mary Lou Friend" <anonymous@.discussions.microsoft.com> skrev i en
meddelelse news:4d9301c402bf$698bdf80$a601280a@.phx.gbl...
> What I will do is create a temporary table in the same
> database with the data from the file.
> Next, I will write a script to (import) insert from the
> temp table to where you want the data to go.
> Mary
>
> commaseparated files
> import of ALL info in
> command...
> the file has an ID
> UPDATE all NAME-cols in
> the file might have
> are in the file, they
> but it would be
> perhaps...? or is the
> within an ActiveX task?

import .sql script into sql server

Hi,

Maybe this is an easy task, but I'm having a really hard time figuring
out how to do this. I'm a complete newbie to SQL Server.

I have a database dump file from MySQL that's in .sql format. I'm
trying to figure out how to import that into SQL Server 2000 so that
I'll be able to manipulate it in a gui format, rather than command
line. I can't find any import that takes a .sql file. I've been
trying to load it into the query analyzer and am also having problems
with that. Initially I had problems because there were question marks
within some of my data. I've removed those but when I run the query I
still get a bunch of syntax errors. Looking at the errors, the syntax
seems correct.

Does anyone know of a way to import a .sql script without having to
import it into the query analyzer? It seems like it should be a
no-brainer.... but perhaps my brain is lacking... I don't know!

Thanks for the help!

--jet

the following are the errors that I get and the associated code:
------------------------
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near ','.

CODE: INSERT INTO answers VALUES
('travelMethodRadio','jackwichita@.montana.com','te lemark'),

The second line is line 17, and it is followed by more insert values.
------------------------
Server: Msg 170, Level 15, State 1, Line 7093
Line 7093: Incorrect syntax near 'a'.

CODE: ('groupSlopeTravelText','kathryn232@.jhmg.com','Thi s is
completely dependent on conditions and if it is a heavily used area or
a fairly pristine area'),

This is just one in a long list of insert values... no idea why there
is a problem with this particular one.
------------------------"jet" <jessey_tase@.yahoo.com> wrote:
> Hi,
> Maybe this is an easy task, but I'm having a really hard time figuring
> out how to do this. I'm a complete newbie to SQL Server.
> I have a database dump file from MySQL that's in .sql format. I'm
> trying to figure out how to import that into SQL Server 2000 so that
> I'll be able to manipulate it in a gui format, rather than command
> line. I can't find any import that takes a .sql file. I've been
> trying to load it into the query analyzer and am also having problems
> with that. Initially I had problems because there were question marks
> within some of my data. I've removed those but when I run the query I
> still get a bunch of syntax errors. Looking at the errors, the syntax
> seems correct.
> Does anyone know of a way to import a .sql script without having to
> import it into the query analyzer? It seems like it should be a
> no-brainer.... but perhaps my brain is lacking... I don't know!
> Thanks for the help!
> --jet
>
> the following are the errors that I get and the associated code:
> -----------------------
--
> Server: Msg 170, Level 15, State 1, Line 17
> Line 17: Incorrect syntax near ','.
> CODE: INSERT INTO answers VALUES
> ('travelMethodRadio','jackwichita@.montana.com','te lemark'),
> The second line is line 17, and it is followed by more insert values.
> -----------------------
--
> Server: Msg 170, Level 15, State 1, Line 7093
> Line 7093: Incorrect syntax near 'a'.
> CODE: ('groupSlopeTravelText','kathryn232@.jhmg.com','Thi s is
> completely dependent on conditions and if it is a heavily used area or
> a fairly pristine area'),
> This is just one in a long list of insert values... no idea why there
> is a problem with this particular one.
> -----------------------
--

jet,

Before answering your question... if you use the ODBC driver for MySQL, you
can use SQL Server DTS to get table structure and data to/from MySQL. IMHO,
it's much easier that way. However, to answer your question...

The problem you're having would be the same going from SQL Server back to
MySQL: they both use extensions to SQL. Query Analyzer is definitely the
tool to use if you want to run a MySQL .sql file vs your SQL Server.
However, some things to keep in mind..

- Neither SQL Server nor Query Analyzer recognize "#" as a comment
character: use "--" instead.

- MySQL escapes single quotes in a string literal with a backslash (\'): SQL
Server escapes them with an extra single quote ('').

- MySQL allows you to "chain" records for an insert (this is the error
you're getting above). When I generate a SQL file from MySQL I use
phpMyAdmin and if I de-select the option "Extended Inserts" it generates an
insert per record instead of a single insert (what you want for SQL Server).
Basically, you're getting the error because you're trying

INSERT SomeTable VALUES ('a1', 'a2', a3'), ('b1', 'b2', b3')

when you want

INSERT SomeTable VALUES ('b1', 'b2', b3')
INSERT SomeTable VALUES ('a1', 'a2', a3')

Hope this helps!

Craig|||Jet,
If you are a newbie to MS SQL Server 2000 and want to get up to speed
really quickly, our videos give you expert instruction on what you really
need to know about SQL Server. It's like reading a 1200 page book in a few
hours.
To answer your question below, a great way to do this is by using a DTS
package. It's easy to write, makes importing as simple as Excel, and can be
re-used.

For full info and examples of using a DTS package, download our videos at
www.technicalVideos.net
Best regards,
Chuck Conover
www.TechnicalVideos.net

"jet" <jessey_tase@.yahoo.com> wrote in message
news:c3fc98c2.0401251736.2fb4db30@.posting.google.c om...
> Hi,
> Maybe this is an easy task, but I'm having a really hard time figuring
> out how to do this. I'm a complete newbie to SQL Server.
> I have a database dump file from MySQL that's in .sql format. I'm
> trying to figure out how to import that into SQL Server 2000 so that
> I'll be able to manipulate it in a gui format, rather than command
> line. I can't find any import that takes a .sql file. I've been
> trying to load it into the query analyzer and am also having problems
> with that. Initially I had problems because there were question marks
> within some of my data. I've removed those but when I run the query I
> still get a bunch of syntax errors. Looking at the errors, the syntax
> seems correct.
> Does anyone know of a way to import a .sql script without having to
> import it into the query analyzer? It seems like it should be a
> no-brainer.... but perhaps my brain is lacking... I don't know!
> Thanks for the help!
> --jet
>
> the following are the errors that I get and the associated code:
> -----------------------
--
> Server: Msg 170, Level 15, State 1, Line 17
> Line 17: Incorrect syntax near ','.
> CODE: INSERT INTO answers VALUES
> ('travelMethodRadio','jackwichita@.montana.com','te lemark'),
> The second line is line 17, and it is followed by more insert values.
> -----------------------
--
> Server: Msg 170, Level 15, State 1, Line 7093
> Line 7093: Incorrect syntax near 'a'.
> CODE: ('groupSlopeTravelText','kathryn232@.jhmg.com','Thi s is
> completely dependent on conditions and if it is a heavily used area or
> a fairly pristine area'),
> This is just one in a long list of insert values... no idea why there
> is a problem with this particular one.
> -----------------------
--