| Author |
Topic |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2011-05-26 : 15:46:08
|
| 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-26 : 15:53:32
|
| 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. |
 |
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2011-05-26 : 16:09:59
|
| 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 |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-26 : 17:42:08
|
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 =',' ) |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2011-05-26 : 22:54:40
|
| 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. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-27 : 07:46:09
|
| 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.aspxI 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. |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2011-05-27 : 10:09:43
|
| 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. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-27 : 12:38:00
|
| 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. |
|
|
|
tata
Starting Member
1 Post |
Posted - 2012-10-25 : 17:35:51
|
| 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. |
|
|
|
moself
Starting Member
1 Post |
Posted - 2012-11-06 : 15:28:52
|
| 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. |
|
|
|
|
Appending to existing data