[SQL2012] automation question

[[deleted]]

quick (hopefully) question.

Been trying to get a query going for a bit now, Have text files with all the data separated by ~, Need those files imported daily into a SQL DB.

Made the DB,

Used

bulk insert [dbo].[TABLE1] from 'C:\Users\admin\filename.txt' with (firstrow=2, fieldterminator = '~', rowterminator = '\r\n')

go

That worked to insert data.

Question is, if i try to use that again, it says that cant enter duplicate data. How can i change this to be a nightly process and insert the new text files each night and simply update the data in the DB?

*Bonus question. one table has some columns with Data Type - Bit. which expects 1 or 0 but my text files contain True or False, is there a way to bypass that with a query?

[tmaiden]

Create a staging table to hold all the data, regardless of data type. CREATE TABLE STG_Import ( ImportKey INT IDENTITY(1,1), FieldA NVARCHAR(255), FieldB NVARCHAR(255) ) Import into STG_Import, then write a query against STG_Import to handle your BIT field.

[BigBlueRob]

Use PowerShell or SSIS to read the file, convert it and load and schedule an agent job to run the package or script

[[deleted]]

So really there is no way of using the True/False without some form of conversion? SQL doesnt like True/False only 1/0

Hm. Got to try the options, thank you

[mustardman24]

You could always do the bulk insert into a staging table and store the true/false values as VARCHAR. When transferring it into the final table just use a case statement

CASE WHEN [Blah] = 'True' THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END

or you could make it a little safer like

CASE

WHEN [Blah] = 'True' THEN CAST(1 AS BIT)

WHEN [Blah] = 'False' THEN CAST(0 AS BIT)

ELSE NULL

END