SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 Appending to existing data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

simflex
Constraint Violating Yak Guru

315 Posts

Posted - 05/26/2011 :  15:46:08  Show Profile  Reply with Quote
Greetings Experts,

I have long struggled with this.

I successfully imported a huge .txt file into our sql server database.

The one thing I have consistently had problem with is appending to that data.

In other words, after the huge .txt file was imported into sql server db, we get a file each day that need to be added to the data we imported into sql server.

I have had such a difficult time figuring out how to do this.

So, in a nutshel, every day, we receive a .txt file and this .txt file needs to be added to the existing data on the db.

How do I set this up in such that data from this .txt file will be imported into sql server and successfully added to existing data in the db already?

I hope I am clear about my issue.

If not, please let me know.

Thanks a lot in advance

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 05/26/2011 :  15:53:32  Show Profile  Reply with Quote
It depends on how you are importing it. If you are using DTS wizard (or SSIS), you have the option to truncate the destination table OR to append the data. Bulk insert lets you do this as well.
Go to Top of Page

simflex
Constraint Violating Yak Guru

315 Posts

Posted - 05/26/2011 :  16:09:59  Show Profile  Reply with Quote
sunitabeck, thank you for the prompt reply.

I am using dts and would have preferred to use the Append option but it is grayed out.

*IF* possible, can you, PLEASE give me an example of using the BULK INSERT option?

Again, thanks a lot
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 05/26/2011 :  17:42:08  Show Profile  Reply with Quote
The Append option is grayed out if the destination table does not exist. If you create the table and then try to insert into that table, that option should be available to you.

Bulk insert is a SQL command that you can use from SSMS (or from windows using sqlcmd). In its most basic form the syntax is like this:
BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail FROM 'f:\orders\lineitem.tbl'
There are several options you can specify, such as the delimiter etc. which are listed here: http://msdn.microsoft.com/en-us/library/ms188365.aspx. For example, if your column delimiter is comma, you would specify it like this:
BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR =','
     )
Go to Top of Page

simflex
Constraint Violating Yak Guru

315 Posts

Posted - 05/26/2011 :  22:54:40  Show Profile  Reply with Quote
Thanks again,very much for your replies.

You said that the Append feature grays out only if the destination table doesn't exist.

Well, as I stated in the original thread, the table already exists because we have already done one huge import.

All we are looking to do now is to add to the existing data via the append or any other method.

So, I am still not sure why it is grayed out.

I will check it out again.

As for the bulk insert, the reason I asked for sample from you is to see *how* you code it to append data to existing table.

I can do bulk insert to the extent you did it but I am not sure if your example can help me accomplish what I am trying to accomplish.

The link by the way is broken.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 05/27/2011 :  07:46:09  Show Profile  Reply with Quote
Sorry about the link. I had an extra period at the end of the link address. Here is the correct link: http://msdn.microsoft.com/en-us/library/ms188365.aspx

I tested the availability of Append option on my SQL 2008 developer edition, and it behaves as I had described earlier. So I am suspecting that it may have to do with the version of SQL you are using. What version are you on?

Bulk insert appends to the existing data. You can test this by creating a test table with just one column, creating a test file with one column and bulk inserting the same file couple of times. You can use the example that I had in my posting (which is from examples on the link that I gave above) pretty much as it is. You would need to, of course, change the database name, the server name etc.
Go to Top of Page

simflex
Constraint Violating Yak Guru

315 Posts

Posted - 05/27/2011 :  10:09:43  Show Profile  Reply with Quote
You are very correct about the append being enabled if a table already exists.

For some unknown silly reason, I was expecting the existing table to be implicitly available.

Once I selected it as the destination table, the append was now selected.

That was neat.

However, do you know why it is giving me the following error:

cannot convert between unicode and non-unicode string data types. ...

All I know is that it derived all the datatypes already setup in the existing table and I thought that with that, all will be well.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 05/27/2011 :  12:38:00  Show Profile  Reply with Quote
This is an indication that in your source file you have unicode characters that you are trying to insert into a non-unicode column in the table. I don't have much experience with unicode/non-unicode issues, but I would expect that if the column in the destination table can be changed to nvarchar, or if you can cast the source data to varchar (which may result in data loss) that would fix it.
Go to Top of Page

tata
Starting Member

1 Posts

Posted - 10/25/2012 :  17:35:51  Show Profile  Reply with Quote
DTS with option- Append Data in SQL 2000. I've SQL 2008- the import/export does not have APpend Data. Could someone tell me where else or how to import data with append data?

Thanks.
Go to Top of Page

moself
Starting Member

USA
1 Posts

Posted - 11/06/2012 :  15:28:52  Show Profile  Reply with Quote
I am useing SQL SErver 2008 and need to be able to append data to a table imported from Access.

The table exists in the database and the Append option is grayed out. HOW do I get the option to be available.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000