4

I'm currently trying to import about 10000 rows (from a CSV file) into an existing table.

I only have 1 column which I'm trying to import, but in my table I have a another column called TypeId which I need to set to a static value i.e. 99E05902-1F68-4B1A-BC66-A143BFF19E37.

So I need something like 

INSERT INTO TABLE ([Name], [TypeId])
Values (@Name (CSV value), "99E05902-1F68-4B1A-BC66-A143BFF19E37")

Any examples would be great.

Thanks

tereško
  • 58,060
  • 25
  • 98
  • 150
D-W
  • 5,201
  • 14
  • 47
  • 74
  • 2
    Could you do this? Import the CSV into a holding table. Then use a normal insert statement to copy the values, plus your static value, into the destination table? *[As CSV files don't have type safety, I generally copy them into a holding table and validate the rows before I even think about inserting the data into a destination table.]* – MatBailie Jun 13 '12 at 13:54
  • Yes, I can import into a temp table and then do an insert, didnt know if 2012 offered any sexy improvements for this common issue – D-W Jun 13 '12 at 14:22

2 Answers2

5

As mentioned above import the data into a temporary table and then insert the value into the actual table

DECLARE @TempTable TABLE (Name nvarchar(max))

 BULK INSERT @TempTable 
 FROM ‘C:\YourFilePath\file.csv’
 WITH ( FIELDTERMINATOR = ‘,’,
 ROWTERMINATOR = ‘\n’
)

INSERT INTO TABLE ([Name], [TypeId])
Select Name,'99E05902-1F68-4B1A-BC66-A143BFF19E37' from @TempTable
praveen
  • 12,083
  • 1
  • 41
  • 49
2

If you are ready to use a tool to do this you can use the SQL Server Import and Export Wizard. You can start the SQL Server Import and Export Wizard from the Start menu, from SQL Server Management Studio, from SQL Server Data Tools (SSDT), or at the command prompt. You can map the destination and source column very easily using this tool. Later on if u wish to update the other column you can do using code.

Saurabh R S
  • 3,037
  • 1
  • 34
  • 44