When copying data in sql, add a column

sql sqlbulkcopy sqldatareader sql-server-2008

Question

I'm bulk inserting certain data from one table into another table using SqlBulkCopy. A SqlDataReader is used by the query to get the data. The only distinction between the tables that counts is that the destination table contains a date column into which the current date must be entered. Column order is handled by the mappings, so this is the only other distinction. The source table does not include this date. How can I include this into the existing process, which is effective without it?

The functioning code at this time 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);
            }

Sql 2008 ENT is the database.

1
4
2/1/2010 8:04:27 PM

Accepted Answer

You may just include it in the SELECT return list:

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

Simply add another ColumnMapping for it after that.

6
2/1/2010 8:03:10 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow