add column while copying data in sql

sql sqlbulkcopy sqldatareader sql-server-2008

Question

I'm using SqlBulkCopy to bulk insert some records from one table into another table. The query is using a SqlDataReader to get the data. The one difference that matters (besides column order which is handled in the mappings) between the tables is that the destination table has a date column into which the current date needs to be added. This date is not in the source table. How can I add this into the current process which is working fine minus this?

Current working code looks like this:

SqlCommand cmd = new SqlCommand("SELECT * from dbo.source", cn);
            SqlDataReader rdr = cmd.ExecuteReader();                

            using (SqlBulkCopy copy = new SqlBulkCopy(cn))
            {
                copy.ColumnMappings.Add(0, 0);
                copy.ColumnMappings.Add(1, 2);
                copy.ColumnMappings.Add(3, 3);
                copy.ColumnMappings.Add(2, 4);
                copy.ColumnMappings.Add(5, 5);
                copy.ColumnMappings.Add(14, 6);
                copy.DestinationTableName = "destination";
                copy.WriteToServer(rdr);
            }

The DB is sql 2008 ENT.

Accepted Answer

You could just add it to be returned by your SELECT:

SELECT *, GETDATE() AS CurrentDate from dbo.source

And then just add another ColumnMapping for it.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why