Implicit queries: created

Showing posts with label created. Show all posts

Wednesday, March 28, 2012

Import in table identity column

I am importing data in the table which has primary key as identity.
Data file doesn't has the data for the column.
I created view which has the column except the identity column trying
to import but getting error
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ID', table 'MyTable; column
does not allow nulls. INSERT fails.
Note: Bulk Insert through a view may result in base table default
values being ignored for NULL columns in the data file.
The statement has been terminated.
Please help.
Thanks
--
FarhanUnless I am missing something, you should be able to import into the table
normally. Don't try to put anything in the Identity column. It will assign
the values automatically. Forget about creating a view to import into.
HTH
Paul
"fsoomro@.chartlinks.com" wrote:

> I am importing data in the table which has primary key as identity.
> Data file doesn't has the data for the column.
> I created view which has the column except the identity column trying
> to import but getting error
> Server: Msg 515, Level 16, State 2, Line 1
> Cannot insert the value NULL into column 'ID', table 'MyTable; column
> does not allow nulls. INSERT fails.
> Note: Bulk Insert through a view may result in base table default
> values being ignored for NULL columns in the data file.
> The statement has been terminated.
> Please help.
> Thanks
> --
> Farhan
>|||This works fine.
use northwind
go
create table t (colA int not null identity(1, 1) unique, colB varchar(25))
insert into t (colB) values('a')
insert into t (colB) values('b')
insert into t (colB) values('c')
execute master..xp_cmdshell N'bcp "select colB from northwind.dbo.t"
queryout d:\temp\test.tbl -Smyserver -T -c'
select * from t
delete t
go
create view myview
as
select colB from t
go
bulk insert northwind.dbo.myview
from 'd:\temp\test.tbl'
with
(
fieldterminator = '\t',
rowterminator = '\n'
)
go
select * from t
go
drop view myview
go
drop table t
go
execute master..xp_cmdshell N'del d:\temp\test.tbl'
go
AMB
"fsoomro@.chartlinks.com" wrote:

> I am importing data in the table which has primary key as identity.
> Data file doesn't has the data for the column.
> I created view which has the column except the identity column trying
> to import but getting error
> Server: Msg 515, Level 16, State 2, Line 1
> Cannot insert the value NULL into column 'ID', table 'MyTable; column
> does not allow nulls. INSERT fails.
> Note: Bulk Insert through a view may result in base table default
> values being ignored for NULL columns in the data file.
> The statement has been terminated.
> Please help.
> Thanks
> --
> Farhan
>sql

Friday, March 23, 2012

Import Flat file to sql server programmatically

Hi,

i want to import flat file data to sql server. i created a package in vb.net. if the import table column is identity means i got

Failure inserting into the read-only column "ID".
Column metadata validation failed.
"component "OLE DB Destination" (10)" failed validation and returned validation status "VS_ISBROKEN".
One or more component failed validation.
There were errors during task validation. error.

how can i rectify this error? or how can i ignore the identity column in coding.

thanks & regards,

sivani

Don't map a column to the identity column in the table. Leave it out.

For instance, in terms of SQL this won't work:

insert into table (ID, Description) values (1,'Row1');

But this will:

insert into table (Description) values ('Row1');

So in your package, don't map a data flow field to the identity column.|||

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

|||

Hi,

i have created a package successfully to import flat file. i got some error while i am importing fixedwidth textfiles. The error is,

No column was specified to allow the component to advance through the file.
component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC02020F0.

and my text file data like this,

00001234561195469 040000 0000 7674491234 47674491234 211252901209030000199804130128431998041301292900003800IXCRD1

00001234568002251234 0400234096321234 0000 8002251234 09782251234 111170401218830000199804130634501998041305345900000400IXCRD1

can anybody give me a solution?

Thanks & regards,

sivani

|||

Did you find an anwer to your question?

Jay

|||

Sivani wrote:

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

I don't understand. Don't do anything with the identity column. Leave it out/alone.|||

Yes, Finally i found the solution

Jayanthi.

Import Flat file to sql server programmatically

Hi,

i want to import flat file data to sql server. i created a package in vb.net. if the import table column is identity means i got

how can i rectify this error? or how can i ignore the identity column in coding.

thanks & regards,

sivani

Don't map a column to the identity column in the table. Leave it out.

For instance, in terms of SQL this won't work:

insert into table (ID, Description) values (1,'Row1');

But this will:

insert into table (Description) values ('Row1');

So in your package, don't map a data flow field to the identity column.|||

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

|||

Hi,

i have created a package successfully to import flat file. i got some error while i am importing fixedwidth textfiles. The error is,

No column was specified to allow the component to advance through the file.
component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC02020F0.

and my text file data like this,

00001234561195469 040000 0000 7674491234 47674491234 211252901209030000199804130128431998041301292900003800IXCRD1

00001234568002251234 0400234096321234 0000 8002251234 09782251234 111170401218830000199804130634501998041305345900000400IXCRD1

can anybody give me a solution?

Thanks & regards,

sivani

|||

Did you find an anwer to your question?

Jay

|||

Sivani wrote:
Hi,
Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.
regards,
sivani

I don't understand. Don't do anything with the identity column. Leave it out/alone.|||

Yes, Finally i found the solution

Jayanthi.

Import Flat file to sql server programmatically

Hi,

i want to import flat file data to sql server. i created a package in vb.net. if the import table column is identity means i got

how can i rectify this error? or how can i ignore the identity column in coding.

thanks & regards,

sivani

Don't map a column to the identity column in the table. Leave it out.

For instance, in terms of SQL this won't work:

insert into table (ID, Description) values (1,'Row1');

But this will:

insert into table (Description) values ('Row1');

So in your package, don't map a data flow field to the identity column.|||

Hi,

Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.

regards,

sivani

|||

Hi,

i have created a package successfully to import flat file. i got some error while i am importing fixedwidth textfiles. The error is,

No column was specified to allow the component to advance through the file.
component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC02020F0.

and my text file data like this,

00001234561195469 040000 0000 7674491234 47674491234 211252901209030000199804130128431998041301292900003800IXCRD1

00001234568002251234 0400234096321234 0000 8002251234 09782251234 111170401218830000199804130634501998041305345900000400IXCRD1

can anybody give me a solution?

Thanks & regards,

sivani

|||

Did you find an anwer to your question?

Jay

|||

Sivani wrote:
Hi,
Thanks for your reply. i want it to do programmatically. Give me some sample code in vb.net to igmore the identity column.
regards,
sivani

I don't understand. Don't do anything with the identity column. Leave it out/alone.|||

Yes, Finally i found the solution

Jayanthi.

Wednesday, March 21, 2012

Import Excel Spreadsheet to SQL 7

Running SQL Server 7 and Excel 2002 on an Win XP Pro machine.
I created a database with two tables, ran a program that added some data to
both tables, then used the Data Transformation Services Export Wizard to
export the tables to an Excel workbook containing each table as a separate
spreadsheet. So far, so good.
I manually made a number of changes to the spreadsheets, altering some of
the values that had been exported, adding some more rows to one of the
spreadsheets and sorting the rows. Now when I go back to SQL Server and use
the Wizard to import the data back into the tables, not all of the data
comes back in. Of about 250+ rows in one of the tables, less than 200 of
them get imported. I don't see any errors when running the wizard. I did a
TRUNCATE TABLE in query analyzer before doing the import to clear all the
rows in each table.
Is there some kind of marker to put in the file that tells the import wizard
where to stop? For example, when printing a spreadsheet, you "set" the
print area. Is there a corresponding function to set the "import" area?
Thanks
John Schneider
When you export to Excel from SQL Server a named range is created on the
Excel worksheet. In Excel select Insert on the menu and then Name. You
should see a name in the dialog box. Click on it and update the range and
then the full data set should get imported to SQL server.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Bingo! Thanks for the reply. I NEVER would have found that setting.
John
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:cP9tbKX0EHA.764@.cpmsftngxa10.phx.gbl...
> When you export to Excel from SQL Server a named range is created on the
> Excel worksheet. In Excel select Insert on the menu and then Name. You
> should see a name in the dialog box. Click on it and update the range and
> then the full data set should get imported to SQL server.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

Import DT_DBDATE into a SQL TAble with datatype of datetime but without the timestamp

I created a SSIS package and creating a derived column named: Date...set datatype as DT_DBDATE....I do not want the timestamp on date...then I want to load this Date into a SQL server database table, with datatype of datetime, but it will load here with the timestamp which I do not want. Any ideas? I did change datatype of the SQL Server Destination datatype to DT_DBDATE but it will change it back to DT_DBTimestamp. thxSQL Server has no data type that contains just a date. If you insert just a date (insert into table (your_datefield) values ('07/16/2007')) you'll get a result stored in the database as '07/16/2007 00:00:00' or something like that. No way around that at the moment. Keep your eyes on SQL Server 2008 though.

Monday, March 19, 2012

Import db from dump with bcp

I am trying to import a database from a dump file.
The dump file is from a sql server db.
I have created the db on another server and want to import the data from the
dump file.
I use the bcp like this :
bcp nnfdb.sa.users in x -Usa -P
nnfdb is the name of the database
users is the name of a table
x is the name of the dump file.
But I get this error :
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'nnfdb.sa.users'.
also I am sure if I have to run the bcp for each table or if I can import
all tables in one bcp run.
Regards
KrisSa is a login. Perhaps you meant nnfdb.dbo.users?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Kris" <Kris@.discussions.microsoft.com> wrote in message
news:963F8255-AD7F-4268-A8F3-3B5570EFCCB0@.microsoft.com...
>I am trying to import a database from a dump file.
> The dump file is from a sql server db.
> I have created the db on another server and want to import the data from the
> dump file.
> I use the bcp like this :
> bcp nnfdb.sa.users in x -Usa -P
> nnfdb is the name of the database
> users is the name of a table
> x is the name of the dump file.
> But I get this error :
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'nnfdb.sa.users'.
> also I am sure if I have to run the bcp for each table or if I can import
> all tables in one bcp run.
> Regards
> Kris|||You'r right, if I use dbo instead of sa I dont get the same error.
So I use :
bcp nnfdb.dbo.users in x -Usa -P -Sdk1052
But it lookes like it is trying to insert everything into the users table.
The dump file is a 20G big file containing the hole db.
I havent made the dump file myself so I don't now how it was created.
Kris
"Tibor Karaszi" wrote:
> Sa is a login. Perhaps you meant nnfdb.dbo.users?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Kris" <Kris@.discussions.microsoft.com> wrote in message
> news:963F8255-AD7F-4268-A8F3-3B5570EFCCB0@.microsoft.com...
> >I am trying to import a database from a dump file.
> > The dump file is from a sql server db.
> >
> > I have created the db on another server and want to import the data from the
> > dump file.
> >
> > I use the bcp like this :
> >
> > bcp nnfdb.sa.users in x -Usa -P
> >
> > nnfdb is the name of the database
> > users is the name of a table
> > x is the name of the dump file.
> >
> > But I get this error :
> > SQLState = S0002, NativeError = 208
> > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> > 'nnfdb.sa.users'.
> >
> > also I am sure if I have to run the bcp for each table or if I can import
> > all tables in one bcp run.
> >
> > Regards
> >
> > Kris
>
>|||> The dump file is a 20G big file containing the hole db.
> I havent made the dump file myself so I don't now how it was created.
You need to know the format of the dump to determine the proper way to
import the data. If the file contains data from multiple tables, it may be
a SQL Server backup file rather than one created with BCP.
One method to determine if the file was created with BACKUP is by running a
Transact-SQL RESTORE FILELISTONLY from Query Analyzer:
RESTORE FILELISTONLY
FROM DISK = 'C:\MyDumps\MyFile.bkp'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kris" <Kris@.discussions.microsoft.com> wrote in message
news:2111D9CB-A350-47CC-BB7C-B8C07F85D5C0@.microsoft.com...
> You'r right, if I use dbo instead of sa I dont get the same error.
> So I use :
> bcp nnfdb.dbo.users in x -Usa -P -Sdk1052
> But it lookes like it is trying to insert everything into the users table.
> The dump file is a 20G big file containing the hole db.
> I havent made the dump file myself so I don't now how it was created.
> Kris
> "Tibor Karaszi" wrote:
>> Sa is a login. Perhaps you meant nnfdb.dbo.users?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Kris" <Kris@.discussions.microsoft.com> wrote in message
>> news:963F8255-AD7F-4268-A8F3-3B5570EFCCB0@.microsoft.com...
>> >I am trying to import a database from a dump file.
>> > The dump file is from a sql server db.
>> >
>> > I have created the db on another server and want to import the data
>> > from the
>> > dump file.
>> >
>> > I use the bcp like this :
>> >
>> > bcp nnfdb.sa.users in x -Usa -P
>> >
>> > nnfdb is the name of the database
>> > users is the name of a table
>> > x is the name of the dump file.
>> >
>> > But I get this error :
>> > SQLState = S0002, NativeError = 208
>> > Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
>> > name
>> > 'nnfdb.sa.users'.
>> >
>> > also I am sure if I have to run the bcp for each table or if I can
>> > import
>> > all tables in one bcp run.
>> >
>> > Regards
>> >
>> > Kris
>>

import database

I need to import a database from MS SQL 2000. I created the database. Then I create the database scripts and run them in sql express but I get this error.

"The specified schema name "username" either does not exist or you do not have permission to use it."

I have also tried to use DTS on MS SQL 2000 to export the data into text file. I do not have the ability to copy the mdf files and attach them in SQL express or make a backup.SQL Server introduced the schema whcih did not exist in SQL Server 200. YOu would either have to rename the object owner in the script to a existing schema (like dbo) or create the appropiate schemas in SQL Server 2005 which were fromer the owners of an object.

HTH, Jens K. Suessmeyer.

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

Hi,

Its self Explainatery message, it means that the Schema or User (Owner) does not exists on the system you are trying to run the script, create a similar user /change the schema in script . Do you have proper privilege !!!? Refer some link below, it will clear your doubts

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=176311&SiteID=1

http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/

http://support.esri.com/index.cfm?fa=knowledgebase.techArticles.articleShow&d=30620

Hemantgiri S. Goswami

|||I made the schema change and it worked. Thanks.|||Thanks for the links it make a little more sense now.|||

Hi,

Thanks for the information, please click on Help full button if you feel answer is usefull.

Hemantgiri S. Goswami

Monday, March 12, 2012

Import data option not visible

Hello All,

I have created a database on my local machine using SQL Server Express 2005. I am trying to export data from an excel file into a table in this database but when i see tasks available for the database there is no option of import data like I assume there should.

Only the following options are visible.

Detach

Shrink

Back Up

Restore

Generate Scripts

Any solutions on how I can get my excel file into the table.

Kiran

Import / Export is not included in the Express edition.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||

Ohh.

Then would my only option be to download a different version like SQL Server compact.

Thanks,

Kiran

|||

Kiranvukkadala wrote:

Then would my only option be to download a different version like SQL Server compact.

No, only SQL Server Standard, Developer, or Enterprise editions. Read the link John provided.

|||

Would any of these editions be free to download from Microsoft. I need only a test database on my machine which uploads tables through excel files. If there is a workaround with express edition it would be great as I already downloaded that version.

Thanks,

Kiran

|||Nope. None are free. Sorry.

The developer edition is only $50 (US) though.

Import Data into XML

I have an XML File created to my specs. How do I import data into XML?
I have a PRN file, tab delimited, that I want to import, or convert
his PRN to Excel, which I can do and then import the Excel data into
XML? I created an XML file based off a DTD template. Now I need to
import data into this XML file.
Please help
Thanks,
Brian
You mean you want to "convert" your data into XML? Well, Office 2003 can do
it for you (using Word or XL). You could also have your own application read
through the file and general XML, or use XSL for generating the XML. Here is
one sample: http://www.devx.com/getHelpOn/10MinuteSolution/20356
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Brian Jorgenson" <bjorgenson@.charter.net> wrote in message
news:34ec3ea7.0407200738.3d09f58c@.posting.google.c om...
> I have an XML File created to my specs. How do I import data into XML?
> I have a PRN file, tab delimited, that I want to import, or convert
> his PRN to Excel, which I can do and then import the Excel data into
> XML? I created an XML file based off a DTD template. Now I need to
> import data into this XML file.
> Please help
>
> Thanks,
> Brian
|||Not convert, but import data into an existing XML file. Is there a way
to join the tags in XML to the field's in an Excel file so the right
data gets imported into the correct XML tag's.
"SriSamp" <ssampath@.sct.co.in> wrote in message news:<#UNqO6obEHA.1656@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> You mean you want to "convert" your data into XML? Well, Office 2003 can do
> it for you (using Word or XL). You could also have your own application read
> through the file and general XML, or use XSL for generating the XML. Here is
> one sample: http://www.devx.com/getHelpOn/10MinuteSolution/20356
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Brian Jorgenson" <bjorgenson@.charter.net> wrote in message
> news:34ec3ea7.0407200738.3d09f58c@.posting.google.c om...
|||XL 2003 allows you to map data in the sheet to specific tags in an XML.
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Brian Jorgenson" <bjorgenson@.charter.net> wrote in message
news:34ec3ea7.0407241902.6ab0c13a@.posting.google.c om...
> Not convert, but import data into an existing XML file. Is there a way
> to join the tags in XML to the field's in an Excel file so the right
> data gets imported into the correct XML tag's.
> "SriSamp" <ssampath@.sct.co.in> wrote in message
news:<#UNqO6obEHA.1656@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
can do[vbcol=seagreen]
read[vbcol=seagreen]
Here is[vbcol=seagreen]
|||Very cool... i forgot that Office System is more XML based. I will
give this a try and kudos to you.
"SriSamp" <ssampath@.sct.co.in> wrote in message news:<eX#iICxcEHA.3728@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> XL 2003 allows you to map data in the sheet to specific tags in an XML.
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Brian Jorgenson" <bjorgenson@.charter.net> wrote in message
> news:34ec3ea7.0407241902.6ab0c13a@.posting.google.c om...
> news:<#UNqO6obEHA.1656@.TK2MSFTNGP09.phx.gbl>...
> can do
> read
> Here is

import data from ODBC to SQL2005

Hi, there;
I want to importing data from ODBC, I created DataReader Source which use a .NET Provide \Odbc Data Provider and connected successfully. My destination is a OLE DB Destination that points to SQL2005. I set the SQL command as "SELECT * from ....".
I also have problem to create new table in SQL2005 using SSIS Import and Export Wizard, it doesn't know the source table table schema (two date type column). So I create the new table manully and run the package, I got error:

SSIS package "Package1.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC02090F5 at Data Flow Task, Source - Query [1]: The component "Source - Query" (1) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Source - Query" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Data Flow Task, Destination - PITest CMF [169]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Task, Destination - PITest CMF [169]: The final commit for the data insertion has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination - PITest CMF" (169)" wrote 0 rows.
Task failed: Data Flow Task
SSIS package "Package1.dtsx" finished: Success.

Can any one know what's wrong here?

And it is quite pain that you have to specify the table name every time you want to create a new table in SQL2005. It is so easy in SQL2000!!!

Thanks.Did you verify that your source query worked and the column mappings metadata is correct? That's where I would start looking.

Import data from MS SQL Server - error

I have a remote database which I need to make a local copy of. I have sucessfully scripted the db and created it locally. however I cannot import the data to the local server. I get this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot truncate table

i think i am doing everything ok but it still wont work.dbo.xxx beacause its being referenced by a foreign key constraint.

Any ideaas welcome.

Gary

Apparently, the process is attempting to TRUNCATE the table -and since there is an established PK-FK relationship on the table, TRUNCATE will not work.

You can either
(a) DROP the FK CONSTRAINT, inport the data, recreate the FK CONSTRAINT, or
(b) USING "DELETE FROM MyTable", remove all of the rows of data, or
(c) Load the Parent table first before loading the child table.

|||

my question is why don't u use backup/restore method to get the database created localy. the only reason for not doing so is .. you don't want all the object created in the local copy... if that is the case then ok... otherwise go for backup/restore method

Madhu

Friday, March 9, 2012

Import data from another table

Hello

I have created a table in mssql.2000 which holds details of names etc. I have also included categories of interest. However the table is growing very big and unmanageable as the list of interest expand.

Instead I would like to create seperate tables for each category of interest within the same database and populate the table with names taken from the Names_Table I could then indicated yes or no if any name is interested in this category.

for example: Art_category.

However, I am unsure how I can import the column of names from the Names_Table to polulate the NameID column in the Art_category table.

I would appreciate advice and possibly a link to step by step tutorial.

Thanks.

Lynn

Not sure whehter this is what you want, but try:

INSERT INTO Art_category (NameID) SELECT names FROM Names_Table

|||

Thank you fou your reply.

Will this automatically update when a new name is added?

|||You can put the insertion in a trigger.

import data

I'm basically a database noob hoping to find a shortcut if at all possible.

I've created an ER diagram for a site I plan to build and have used that to
create the database in MS SQL 2000.

Now I want to import various data from (basically) excel spreadsheets (for
example, company names from a different sql dbase, addresses from excel and
phone numbers from a csv file) into the new database.

I have tried various methods outlined on web sites (including DTS) and have
learned I need to go back and create default values for essentially every
non-null field if I am to update literally any linked table.

My question is this: Is there any easy way/program via which I can import
the data to two different tables -- i.e. address into one table and
corresponding city into another table -- and have the relationship(s)
continue?

Or alternatively is there a "better/easy" way to do it inside sql once I
import the entire data into it's own single table?

Not to belabor the point (versus to more fully explain), but say I have two
data sets

[ Company Name | Address ]

and

[ Company Name | Phone ]

and I want to import them both into a database with separate three tables:

1. Company Name
2. Address
3. Phone

What is the least labour intensive way to effect this??

Thanks in advanceAlso, (though it is the same issue essentially) what if the first
dataset is Company Name, Address, City where each different city is
listed from 1 to ? number of times -- is there a way the dbase can
know that say New York only needs to be added once to a City table and
then the other tables just have a reference to that ID in the city
table?

"Mark S" <bob@.bob.comwrote in message
news:GVc_h.156992$aG1.70770@.pd7urf3no...

Quote:

Originally Posted by

I'm basically a database noob hoping to find a shortcut if at all
possible.
>
I've created an ER diagram for a site I plan to build and have used that
to create the database in MS SQL 2000.
>
Now I want to import various data from (basically) excel spreadsheets (for
example, company names from a different sql dbase, addresses from excel
and phone numbers from a csv file) into the new database.
>
I have tried various methods outlined on web sites (including DTS) and
have learned I need to go back and create default values for essentially
every non-null field if I am to update literally any linked table.
>
My question is this: Is there any easy way/program via which I can import
the data to two different tables -- i.e. address into one table and
corresponding city into another table -- and have the relationship(s)
continue?
>
Or alternatively is there a "better/easy" way to do it inside sql once I
import the entire data into it's own single table?
>
>
>
Not to belabor the point (versus to more fully explain), but say I have
two data sets
>
[ Company Name | Address ]
>
and
>
[ Company Name | Phone ]
>
and I want to import them both into a database with separate three tables:
>
1. Company Name
2. Address
3. Phone
>
What is the least labour intensive way to effect this??
>
Thanks in advance
>
>
>

|||Mark S wrote:

Quote:

Originally Posted by

Not to belabor the point (versus to more fully explain), but say I have two
data sets
>
[ Company Name | Address ]
>
and
>
[ Company Name | Phone ]
>
and I want to import them both into a database with separate three tables:
>
1. Company Name
2. Address
3. Phone
>
What is the least labour intensive way to effect this??

I would create a table with columns (name, address, phone), import
into it, then use queries to copy its data to the other tables.

Wednesday, March 7, 2012

Import ASCII Data

I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the data
into tables.

What I would like to do is create an automated import function in SQL.

I am new to SQL, can anyone point me in the direction I should look to find
out how I could perform this task?

Using SQL 2005.

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via http://www.sqlmonster.commattc66 via SQLMonster.com (u16013@.uwe) writes:

Quote:

Originally Posted by

I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the
data into tables.
>
What I would like to do is create an automated import function in SQL.
>
I am new to SQL, can anyone point me in the direction I should look to
find out how I could perform this task?
>
Using SQL 2005.

There are a couple of alternatives. There is BCP (command-line tool)
and BULK INSERT (T-SQL statement) which work very similarily. Their
good as long as the files have one entry for each file in each record,
and there are no headers.

You can use the Import Wizard in SQL Server Management Studio. As with
all graphical tools, it's good for a one-off, but it's really a good
place if you need to do this on a regular basis. The Import Wizard requires
that SSIS (see below) is installed.

And then there is SQL Server Integration Service (SSIS), the member of the
SQL Server family that is all about importing and exporting data and
transforming it on the way. I have not used SSIS or its predecessor
myself, so I don't really know what it's so fantastic. (Being an old-
timer, I get by very well with BCP and BULK INSERT.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||To add to Erland's response, you can use a SQL Agent job to schedule the
import process. In the case of a package created with SSMS or the BI dev
studio, there is a specialized SQL Server Integration Services step type
that allows you to specify the desired package and run time settings via a
GUI.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"mattc66 via SQLMonster.com" <u16013@.uwewrote in message
news:65826d5d02090@.uwe...

Quote:

Originally Posted by

>I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the
data
into tables.
>
What I would like to do is create an automated import function in SQL.
>
I am new to SQL, can anyone point me in the direction I should look to
find
out how I could perform this task?
>
Using SQL 2005.
>
Thanks
Matt
>
--
Matt Campbell
mattc (at) saunatec [dot] com
>
Message posted via http://www.sqlmonster.com
>
>

Import and Export Wizard: transferring multiple tables from SQL Server 2005 to SQL Server 2000

Hi!

I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.

I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)

Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.

Basically the wizard creates the following Control Flow tab entries (in processing sequence order):

an Execute SQL Task: NonTransactableSql an Execute SQL Task: START TRANSACTION a Sequence Container: Transaction Scoping Sequence, which contains an Execute SQL Task: AllowedToFailPrologueSql an Execute SQL Task: PrologueSql a Foreach Loop Container, which contains a Transfer Task with an icon I did not notice in the Toolbox an Execute Package Task: Execute Inner Package an Execute SQL Task: EpilogueSql an "on success" arrow to an Execute SQL Task: COMMIT TRANSACTION an Execute SQL Task: PostTransaction Sql an "on failure" arrow to an Execute SQL Task: ROLLBACK TRANSACTION an Execute SQL Task: CompensatingSql

Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?

This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.

So now I'm trying to understand why the Wizard created this more-complex package.

Any help will be appreciated, including references to non-Microsoft books/websites/etc.

Thanks in advance.

Dan

Hi Dan,

you can also have a package with 50 parallel data flows built if you uncheck the "Optimize for Many Tables" checkbox. That might be easier for you to edit. This solution does not scale too well with "really" too many tables so we had to emloy the complex package you are referring to. The metadata is stored in the XML file you mentioned and the transfer task goes through that file and generates simple data flows on the fly and executes them one by one.

HTH.

|||

Bob,

Thanks.

I also now know how to get the "50 parallel data flows" that is easier to edit.

I don't understand, though, how the metadata in the XML would "tag along" if I were to save the SSIS package to the File System and copy it to a network drive, where others could use it.

Dan

|||

You would need to copy all the associated files (like the XML with metadata) and tweak the connections to point to the new location.

Thanks.

|||

Bob,

Thanks again.

So it seems the Wizard creates an SSIS package, and also whatever files are needed to support that package, e.g., the XML file with the table names and structures (if a CREATE is necessary). The user of the Wizard must be smart enough to realize that anything in the Connection Manager must accompany the SSIS package -- and that such "temporary folder" files are essential to the task (so they should not be deleted by any "housekeeping" effort on the PC). That's good to know.

I would have expected such files to end up in someplace like the BIN folder found in the same folder where the SSIS package is created. If it isn't a long explanation, maybe you might share why the development team did not use the BIN folder for such files. (I am not wanting to be "nasty" -- just curious about how such decisions are made by the development team.)

Dan

|||

Hi Dan,

the transfer tables task was not initially designed to be used by the wizard. It was built by SMO team to allow copying tables using their APIs. When used that way the internal package is invisible and it makes sense to put the additional files to the temp folders. Later, we realized it might be suitable for our purpose. We weren't sure how often our users would actually want to preserve this package.

We realized it is a problem now and found some additional issues with the table transfer provider task, so we are looking into simplifying the wizard generated packages for Katmai.

Thanks,

-Bob

|||

Bob,

Thanks for the response. I truly appreciate the help the Wizard has given me on many occasions.

I have learned a lot from the Wizard, using it to convert my SQL Server 2000 DTS Packages to SSIS, and learning from the packages it created.

Since you mentioned some new features, is there any current plan to enhance the Execute Package Utility so that it can accommodate the movement of data from MS Access 2003 SP2 to SQL Server 2005? I can do that when I use Visual Studio 2005, but when I try to execute the package with the Execute Package Utility I get many errors of the form

Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion 1" (49).

The only data conversion I perform is double-byte characters to single-byte characters.

(Maybe I should ask this in a separate thread?)

Dan

|||

Are trying to run the package on the same machine it worked from the designer? What edition of the product you have installed?

It does not seem like new features are needed for this. You just need a proper edition of the product on the machine where you run the package.

Thanks.

|||

Hi Bob,

I'm having a similar issue as Dan, with two notable differences. First, I have over 300 tables to transfer and Second, most of the tables have identity columns.

When I went through the wizard, with "Optimize for Many Tables" checked, it ignored the adjustments to accept the identity values and created new values. Is there a way to adjust this script to accept the Identity values without setting up 300+ parallel data flows? The components are identical to what Dan described in the first posting.

If this can't be done, other suggestions are welcome.

Thanks - Gary

|||

Hi Gary,

unfortunately you have hit another issue with the transfer tables task I was referring to in the previous post. Currently, it is not possible to pass the identitty column settings when the "Optimize for many tables" option is selected.

The best workaround I can offer is to copy your tables in multiple batches (50 tables each should work, it might take up to 100 depending on your hardwear but you would need to test it) with unchecked "Optimize for many tables" option.

Thanks.

|||

Bob,

Thanks for your reply.

Yes, I am trying to run the package from the same machine it worked from the designer. But I am trying to run a "file system" copy of the package that I placed on the network drive. I am out of the office at the moment, so I cannot try running the exact copy on my PC hard drive -- but I will be back in the office in a few days, and can try doing so at that time.

We upgraded to SP2 a month or two ago, for SQL Server 2005. Did you need more "edition" information? If so, I will provide it on Friday, or so.

I just figured it (transfer from Access, and convert Access tables with single-byte characters to double-byte characters, as seem to be usual for SSIS input, then back to single-byte characters for placement in the SQL Server 2005 tables) was a capability that went beyond the intent of the "standalone" package runner (outside Visual Studio), Execute Package Utility.

I am pleased to learn that I may not need to use Visual Studio 2005 to perform this movement of data from Access to SQL Server 2005.

Dan

|||

Bob,

I am back at my desk, where I tried to run the SSIS package that moves approx. 50 tables from MS Access to SQL Server 2005.

The Access version is 2003 (11.6566.8132) SP2.

The SQL Server version is 9.0.3042

The only version information I see in the About box for "About DTExecUI" is "Version: 1.0". Is there some other place I should be seeking version information for this product?

Dan

|||

Dan,

I was asking about the edition of your SQL server instalation; is it Developer, Standard or Enterprise edition?

Thanks.

|||

Bob,

We have the Enterprise edition of SQL Server 2005 in the environment where I am trying to perform the task.

Dan

|||

Have you installed the entire SSIS module on all of those machines as well?

Thanks,

Bob

Import and Export SQL 2005 Maintenance Plan

I had created a maintenance plan and configure and scheduled my maintenace plan to run. I would like to save this package as a file into a source control. SO I use SSIS to export the package under stored packages->MSDB->Maintenace Plan. After that, I wanted to test my import process. So I deleted the packages under SSIS ->stored packages->MSDB->Maintenance plan and I use the import to add the package from my previously exported package stored in a file .dtxs extention.

So the problem is, after I imported my package. I lost the configured schedule and the job runs without doing anything. When I try to go in and make changes to the package, by adding a new schedule under SQL server -> management->maintenance plan. I receive a odd error message and it doesn't allow me to save the package.. The error message I got is...."GUID should contain 32 digits with 4 dashes (xxxxx-xxx-xx...)"

So my questions are: 1. why did the re-import loses the originally configure job run schedule. 2. why doesn't the re-import package works by backing up database as it was first setup. 3. why I cannot re-edit this package and saving the package error out?

Thank you for reading and for your helps! --Jon

I hope this should help. I have the same problem and working out on it

http://support.microsoft.com/default.aspx/kb/922651

http://sqlug.be/blogs/drivenbysql/archive/2006/10/21/374.aspx

|||I had similar issues. SSIS wasn't installed first, so I installed it then starting to get that GUID error. It was a simple fix actually. In studio, got to SQL Server Agent, Jobs and it had jobs for all my deleted plans. I cleared out the jobs, then created a brand new plan and it worked fine. I guess the errors created when I tried to create a plan prior to the SSIS installation caused it not to clear the jobs.

Though, this may or may not be what happened to you.

Import and Export SQL 2005 Maintenance Plan

Thank you for reading and for your helps! --Jon

I hope this should help. I have the same problem and working out on it

http://support.microsoft.com/default.aspx/kb/922651

http://sqlug.be/blogs/drivenbysql/archive/2006/10/21/374.aspx

Though, this may or may not be what happened to you.

Wednesday, March 28, 2012

Import in table identity column

Friday, March 23, 2012

Import Flat file to sql server programmatically

Import Flat file to sql server programmatically

Import Flat file to sql server programmatically

Wednesday, March 21, 2012

Import Excel Spreadsheet to SQL 7

Import DT_DBDATE into a SQL TAble with datatype of datetime but without the timestamp

Monday, March 19, 2012

Import db from dump with bcp

Import db from dump with bcp

Import db from dump with bcp

import database

Monday, March 12, 2012

Import data option not visible

Import Data into XML

import data from ODBC to SQL2005

Import data from MS SQL Server - error

Friday, March 9, 2012

Import data from another table

import data

Wednesday, March 7, 2012

Import ASCII Data

Import and Export Wizard: transferring multiple tables from SQL Server 2005 to SQL Server 2000

Import and Export SQL 2005 Maintenance Plan

Import and Export SQL 2005 Maintenance Plan

Implicit queries

Blog Archive

About Me