I have this flatfile from which I'm loading data into a table manually using BCP command from SQL Command prompt.
Now that we have migrated our database to Visual Studio 2010, I'm wondering how should we be specifying this command in the Post deployment script, so that the data gets loaded into the appropriate table after the database and tables are created.
Currently used bcp command is mentioned below:
bcp test.dbo.tabletest in tabletest_FlatFile.txt -f tabletest_FormatFile.xml -k -T -Ujohn -Sjohn\johninstance
Also I currently have bunch of insert statements in post deployment script to populating lookup data in couple of tables.
The code is as follows:
IF (EXISTS(SELECT * FROM [dbo].[tabletest]))
DELETE FROM [dbo].[tabletest]
INSERT tabletest(testcode, testdescription) VALUES ('xxx', NULL);
INSERT tabletest(testcode, testdescription) VALUES ('yyy', NULL);
I have included the .xml and .txt file included in the above question in "Scripts" folder under the db project (C:\DB_Proj\Scripts).
When I give the project to developers, they might choose to save the project file in a path different (D:\XYZ\).
When the application developer opens the delivered DB project in her Visual studio 2010 and says deploy, will the bcp command in Post deployment script identify the .txt and .xml file in right physical location (i.e., D:\XYZ\Scripts\ folder)?
If no, is there a way to give relative path in the post deployment script, such that the bcp works fine irrespective of where the application developer chooses to save the database project I deliver?