August 15, 2013 at 3:22 pm
I am thinking tools that are free exist to script data and structures from Standard SQL to SQL CE in proper format. I have an example below that describes what I am looking for. If there is an easier way to do this I would appreciate it.
ie instead of scripting data this way:
If Exists (Select 1....)
UPDATE
ELSE
Insert
CE I would need something along these lines:
INSERT INTO Table VALUES (...)
SELECT (........)
WHERE NOT Exists (SELECT ........)
-- INSERT with Default value if not exist. Next, UPDATE it
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
September 16, 2013 at 9:19 am
When it comes to writing data scripts the pain is about the same for CE as standard and Express. Count on there being nothing in CE to help you. But you are scripting data from to CE so there might be things in Standard to help.
Structures:
As far as this goes SQL Server Management Studio can script out table structures for you. You would have to clean up the scripts so that create table statement complies with CE rules. You won't have that many tables for CE. Slightly painful but you can handle that.
Data:
Your key comparison technique (what you show in your question) might not matter. What runs scripts in CE is what is important. If you are doing things through management studio you can at least observer what works. Running scripts on a device should be avoided. The tool is so dismal I won't even go into it.
My way around was to write an application that read the data from some file. Then I would look up the row. If it did not exist I would create an INSERT statement. If the row does exist I would create an UPDATE statement.
An alternative technique is to create a DELETE statement for each row and then an INSERT statement as you are pretty sure that the row won't exist but foreign keys might prevent you from deleting some rows.
I know this sounds bad, and it is. We faced the same thing with CE. We had to put all of the smarts into the application doing the data load and using CE only as storage.
ATBCharles Kincaid
September 16, 2013 at 9:45 am
Charles - Thanks for the reply. That is exactly what I did. Made a sproc that reads the tables data and scripts it for CE but it is customized for each table. It would have been nice to have made a sproc to handle all the tables in a list.
September 16, 2013 at 9:54 am
Yes generalized procedures are harder to write as you have to take everything into consideration. Look at each one of your procedures you will start to see common patterns. You can get inspiration from your own work.
ATBCharles Kincaid
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply