Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Standard SQL scripting to CE Expand / Collapse
Author
Message
Posted Thursday, August 15, 2013 3:22 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:27 PM
Points: 425, Visits: 617
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'

Post #1484927
Posted Monday, September 16, 2013 9:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:41 AM
Points: 809, Visits: 1,996
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.


ATB

Charles Kincaid

Post #1495149
Posted Monday, September 16, 2013 9:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:27 PM
Points: 425, Visits: 617
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.
Post #1495160
Posted Monday, September 16, 2013 9:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:41 AM
Points: 809, Visits: 1,996
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.

ATB

Charles Kincaid

Post #1495166
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse