SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Standard SQL scripting to CE


Standard SQL scripting to CE

Author
Message
JKSQL
JKSQL
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 700
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'


Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1689 Visits: 2384
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
JKSQL
JKSQL
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 700
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.
Charles Kincaid
Charles Kincaid
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1689 Visits: 2384
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search