Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hargifex
Starting Member
2 Posts |
 Posted - 2006-07-11 : 17:04:53
|
| I have a BCP operation where a user provides data that is saved to a file and imported into a temp table.The files are 10k rows of data or more.Is it possible to append, during the bcp process, additional data? It would be ideal to "tag" the inserted data by appending to each inserted temp table row the user's system id (note that this is not a value the user can provide in their file). Essentially I would define the temp table to have a UserId field in addition to the other fields, which I would populate with data not contained in the bcp file itself.Does anyone have a way to do this that doesn't involve iterating through all the rows or modifying each row in the bcp file itself? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-11 : 17:09:53
|
| Where would the UserId data come from? How would you know which row to associate it with?Tara Kizeraka tduggan |
 |
|
|
Hargifex
Starting Member
2 Posts |
Posted - 2006-07-11 : 17:25:58
|
| I'm asking this question in proxy for another developer, so I'll preface my response by saying I hate the UI requirements, but I can't change them.The user copies a subset of data from an excel document into a textarea on a website and submits the data. The pasted data by default is tab delimited with a carriage return at the end of each line.The user id would be available in the session on the website page when the data is submitted.Assuming the temp table was defined with the user id as the first column, that's where the user id would be appended. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-07-11 : 17:31:11
|
| What I would do is create a staging table that matches the layout of the bcp file. Bcp the data into this staging table. Place the UserId from the session into a variable. Then use T-SQL to move the data from the staging table into the actual table that contains this UserId column:INSERT INTO ActualTable(Column1, Column2, ..., UserId)SELECT Column1, Column2, ..., @UserIdFROM StagingTableYou could also use a format file instead of the staging table. I prefer to stay away from foramt files though.Tara Kizeraka tduggan |
|
|
|
|
|
|
|
|
BCP and inserting an additional row