correct practices with SQL Server

I am hoping someone can give some advice on the following things:

I have read a few times about a data access layer in an n-tier application. I am assuming that this should be done

using sprocs. Is there an advantage of using sprocs instead of views ( in situations where the same thing could

be accomplished using either)? Will a sproc run faster than a view? Can any share any info?

Are sprocs best suited for data access and to enforce business rules?

I know SQL Server has reserved words that shouldn't be used. I am wondering what the best thing to do is

in the following situation? What is the best way to handle storing a customer or clients address? I am working from a book that shows the name of a column as "Address". I have found that with SQL Server 2005

Express that this is a reserved word(it is shown in blue in the query window). I want to keep my names short. I am trying to avoid a name like "StreetAddress". Is my book teaching bad habits?

...........................................thanks...........................................................

Views are generally better than stored procedures where they could be used interchangeably, especially considering that you can create indexes on views.

While it makes sense to want to use address for a customer's address, the meaning (in a computer) of the word is too general. It could mean a customer's home address, delivery address, the data address of some memory block on the client side, the IP address of another server, really almost anything. Ideally, you would want to follow naming conventions that enable other developers to immediately draw as much information as they can out of a table (e.g. customer name, customer address, customer ID. This will make it much easier to join tables (e.g. joining customer table with business table might have 2 address fields that need to be resolved). One possible naming convention is to consider the table name (e.g. customers) as an abstract object or schema for a data set and name everything by "<schema> <object name>" you could use underscores if you were more comfortable with unspaced column names. Ideally, this will make it much easier to read complex joins as the project grows and will make maintenance much less of a headache -- since there will be fewer questions like "which address do you mean: the one in customer data, business data, the address of the pointer, or the shipping address of the order in question?".

In general, all books teach both good and bad habits, unfortunately. Most SQL books published over the past 10 years have SQL Injection attacks available against all their examples, for instance. Like grade school -> high school -> college, we have to accept that some percentage of the information we learn in books or from teachers is just wrong. That doesn't mean it isn't practical to use as a learning tool, it is just not perfect in practice. This is sort of the scientific method, work with what you know until you learn something better, then paradigm shift and continue. In the long run, there is no perfect book for any given domain space. The only way to grow is to continuously seek out information, as you are doing now.

Hope that helps,

John

|||

So, it seems like the advantage of using a view with an index would be to increase performance. Should stored procedures be used only when there is a need to write to the database? Are these deductions correct?

I ended up doing what you suggested and changed it to CustAddress and EmpAddress.

........................................Thanks for the advice

Copying stored procedures to another database

Hi,
We have recently deployed our application into a parallel production
environment which will eventually become the main production environment. We
have had to make some changes to a number of CLR (VB) stored procedure,
which we've deployed and tested in production. Now we are ready to deploy
these stored procedures to our new production environment. Unfortunately, we
can't connect deploy remotely, and we can't perform a backup restore because
we need to keep the production data.
Is there a way that we can copy the stored procedures only from our
development server to our production server? They're CLR stored procedures,
so we can't script them out either.
Thanks> They're CLR stored procedures, so we can't script them out either.
Why now? Script the assembly, which will in your script file have the hex co
de for the assembly
instead of the file reference. Then also script your objects created from th
at assembly...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <r_miller@.ozemail.com.au> wrote in message news:u042u2VQIHA.5288@.TK2MSFTNGP04.phx.gbl.
.
> Hi,
> We have recently deployed our application into a parallel production envir
onment which will
> eventually become the main production environment. We have had to make som
e changes to a number of
> CLR (VB) stored procedure, which we've deployed and tested in production.
Now we are ready to
> deploy these stored procedures to our new production environment. Unfortun
ately, we can't connect
> deploy remotely, and we can't perform a backup restore because we need to
keep the production
> data.
> Is there a way that we can copy the stored procedures only from our develo
pment server to our
> production server? They're CLR stored procedures, so we can't script them
out either.
> Thanks

Copying stored procedures to another database

Hi,
We have recently deployed our application into a parallel production
environment which will eventually become the main production environment. We
have had to make some changes to a number of CLR (VB) stored procedure,
which we've deployed and tested in production. Now we are ready to deploy
these stored procedures to our new production environment. Unfortunately, we
can't connect deploy remotely, and we can't perform a backup restore because
we need to keep the production data.
Is there a way that we can copy the stored procedures only from our
development server to our production server? They're CLR stored procedures,
so we can't script them out either.
Thanks> They're CLR stored procedures, so we can't script them out either.
Why now? Script the assembly, which will in your script file have the hex code for the assembly
instead of the file reference. Then also script your objects created from that assembly...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <r_miller@.ozemail.com.au> wrote in message news:u042u2VQIHA.5288@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have recently deployed our application into a parallel production environment which will
> eventually become the main production environment. We have had to make some changes to a number of
> CLR (VB) stored procedure, which we've deployed and tested in production. Now we are ready to
> deploy these stored procedures to our new production environment. Unfortunately, we can't connect
> deploy remotely, and we can't perform a backup restore because we need to keep the production
> data.
> Is there a way that we can copy the stored procedures only from our development server to our
> production server? They're CLR stored procedures, so we can't script them out either.
> Thanks

Copying database over network

Hi,
I'm extremely new to sql , using sql 2000.
I have an application server and a database sitting on a NAS. I would like
to copy the database from the NAS over the network to another server (test
server). I believe with the application server running I would have to detac
h
the data base. If I do not start-up the application server and just do a cop
y
from the NAS to the test server, would this work? and naturally with out
detaching(because there is nothing to detach it from). and I would also like
to give the database a different name. I hope I explaned this correctly. Any
help would be appreciated. Thank You in advance.
Greg newbieConsider using BACKUP and RESTORE. BACKUP lets you backup the database to a
file, which you can move to a different machine, and restore it with a new
name if you prefer, using the RESTORE command. See SQL Server Books Online
for more information and examples on these commands.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:E71A2217-AB1E-49CB-9F5D-FBA2B45FAD88@.microsoft.com...
> Hi,
> I'm extremely new to sql , using sql 2000.
> I have an application server and a database sitting on a NAS. I would
> like
> to copy the database from the NAS over the network to another server (test
> server). I believe with the application server running I would have to
> detach
> the data base. If I do not start-up the application server and just do a
> copy
> from the NAS to the test server, would this work? and naturally with out
> detaching(because there is nothing to detach it from). and I would also
> like
> to give the database a different name. I hope I explaned this correctly.
> Any
> help would be appreciated. Thank You in advance.
> --
> Greg newbie

Copying database over network

Hi,
I'm extremely new to sql , using sql 2000.
I have an application server and a database sitting on a NAS. I would like
to copy the database from the NAS over the network to another server (test
server). I believe with the application server running I would have to detach
the data base. If I do not start-up the application server and just do a copy
from the NAS to the test server, would this work? and naturally with out
detaching(because there is nothing to detach it from). and I would also like
to give the database a different name. I hope I explaned this correctly. Any
help would be appreciated. Thank You in advance.
--
Greg newbieConsider using BACKUP and RESTORE. BACKUP lets you backup the database to a
file, which you can move to a different machine, and restore it with a new
name if you prefer, using the RESTORE command. See SQL Server Books Online
for more information and examples on these commands.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:E71A2217-AB1E-49CB-9F5D-FBA2B45FAD88@.microsoft.com...
> Hi,
> I'm extremely new to sql , using sql 2000.
> I have an application server and a database sitting on a NAS. I would
> like
> to copy the database from the NAS over the network to another server (test
> server). I believe with the application server running I would have to
> detach
> the data base. If I do not start-up the application server and just do a
> copy
> from the NAS to the test server, would this work? and naturally with out
> detaching(because there is nothing to detach it from). and I would also
> like
> to give the database a different name. I hope I explaned this correctly.
> Any
> help would be appreciated. Thank You in advance.
> --
> Greg newbie

Copying database over network

Hi,
I'm extremely new to sql , using sql 2000.
I have an application server and a database sitting on a NAS. I would like
to copy the database from the NAS over the network to another server (test
server). I believe with the application server running I would have to detach
the data base. If I do not start-up the application server and just do a copy
from the NAS to the test server, would this work? and naturally with out
detaching(because there is nothing to detach it from). and I would also like
to give the database a different name. I hope I explaned this correctly. Any
help would be appreciated. Thank You in advance.
Greg newbie
Consider using BACKUP and RESTORE. BACKUP lets you backup the database to a
file, which you can move to a different machine, and restore it with a new
name if you prefer, using the RESTORE command. See SQL Server Books Online
for more information and examples on these commands.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:E71A2217-AB1E-49CB-9F5D-FBA2B45FAD88@.microsoft.com...
> Hi,
> I'm extremely new to sql , using sql 2000.
> I have an application server and a database sitting on a NAS. I would
> like
> to copy the database from the NAS over the network to another server (test
> server). I believe with the application server running I would have to
> detach
> the data base. If I do not start-up the application server and just do a
> copy
> from the NAS to the test server, would this work? and naturally with out
> detaching(because there is nothing to detach it from). and I would also
> like
> to give the database a different name. I hope I explaned this correctly.
> Any
> help would be appreciated. Thank You in advance.
> --
> Greg newbie

Copying Database from 2000Server Machine To 2003 Server Machine

Hi All;

We are going to change our application server. We will copy all of our
Database from Mic 2000 Server OS to Mic 2003 Server OS. I found an
article that how to move all the folders from same OS. My question is
that Would the 2003 OS be a problem when we copy all of the MC SQL 2000
folders to New OS ? Has anybody done this before? Could you gimme your
suggestions please?

Thanks

ASAHi

You can copy the user databases(first detach them and then re-attach them at
destination (sp_attachdb), but you can not copy the EXE's and binaries. You
need to run an install on the new server. SQL Server is not just about
files, it involves registry entries and updates to MDAC etc.

Regards
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@.epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"laststubborn" <arafatsalih@.gmail.com> wrote in message
news:1116865451.858663.71740@.o13g2000cwo.googlegro ups.com...
> Hi All;
> We are going to change our application server. We will copy all of our
> Database from Mic 2000 Server OS to Mic 2003 Server OS. I found an
> article that how to move all the folders from same OS. My question is
> that Would the 2003 OS be a problem when we copy all of the MC SQL 2000
> folders to New OS ? Has anybody done this before? Could you gimme your
> suggestions please?
> Thanks
> ASA

copying data from Microsoft Access to SQL Server

Hello

I am developing a web application that will allow users to upload a .mdb file and from that file I need to populate an SQL database. I know the table name of the .mdb file, but I am unclear how to structure my data access layer correctly. How do I pull data from the .mdb file and once I have it how do i populate the SQL database?

Any advice would be greatly appreciated.

thanks!

There is one problem with moving Access data on the fly Access let people to just point and click what they wish, if that is your situation you need DTS because in SQL Server your data must be clean and relational. So if you have clean table definitions and clean inserts you could just use OpenRowset or OpenQuery. If not then DTS is your friend. So check your setup and post specifics so I can give you the resources as needed. Hope this helps.

copying data and structure from one database to another

Hi all!

I have an application that needs to copy the database structure from
one database to another without using the "Generate SQL Script"
function in Enterprise Manager. I'd like to do this from within a
stored procedure. Can someone recommend the best approach for this?
I've seen references to using SQL-DMO from a stored procedure using the
sp_OA* procs in other postings to this group but was wondering if there
was an easier way? Can I use bcp and then use xp_cmdshell from within
my stored procedure? It's not clear to me from the documentation
whether bcp copies both structure and data or just data? Is there a
better way?

Thanks in advance for any help!
Karen[posted and mailed, posted and mailed]

(kjphipps_377@.hotmail.com) writes:
> I have an application that needs to copy the database structure from
> one database to another without using the "Generate SQL Script"
> function in Enterprise Manager. I'd like to do this from within a
> stored procedure. Can someone recommend the best approach for this?
> I've seen references to using SQL-DMO from a stored procedure using the
> sp_OA* procs in other postings to this group but was wondering if there
> was an easier way? Can I use bcp and then use xp_cmdshell from within
> my stored procedure? It's not clear to me from the documentation
> whether bcp copies both structure and data or just data? Is there a
> better way?

bcp copies only the data.

If you absolutely must copy table definitions and all from a stored
procedure, you are in for a painful exercise. I'd guess that DMO is
the way to go. You could read the system tables and construct SQL
from there, but that would be even more difficult. Particularly if
you need to take in regard that a stored procedure could extend over
more than 4000 characters.

But overall, I would recommend you to review the requirements. T-SQL
is simply not the right tool do this. If you absolutely must fire
a stored procedure, I would recommend writing a program in Perl,
VBscript or whatever, and call that program from xp_cmdshell. But it
goes without saying that it would be better to run this from the
application directly.

Also when running from an application, DMO may be the best pick. I
don't have any experience of DMO myself, so I don't know for sure
whether there is any built-in scripting facilities, but I would
expect there to be.

The general for creating database, is to keep code under source
control, and build the database from the version-controlled scripts.

To copy the data, bcp would still be necessary, but that's the easy
part of it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

copying a database across servers

I'm developing a web application locally using SQL Server 2000. When it's time for me to throw it up on my host, how do I copy the database from my computer to their server? Thanks.use the DTS tools provided with SQL Server (import/export data).

What I usually do is script the database structure (and objects), set that up and check it over, THEN transfer the data, but whether you want to do it that way is up to you

j|||Another way is to simply stop your sql server and copy the database and log files to the other location and then attach the database.

Regards|||There are multiple ways as in using DTS or other tools. BUt if the other server is far apart and you donot want to use the internet bandwidth then I feel the best bet is to backup the database and then transfer/FTP the DB backup to be restored into the destination server. Copying the data and the log files are fine. But not the cleanest of approaches AFAIK.|||All solutions provided here are good, I would personally go with Atrax' Scripting solution, it is probably the best way to do it if you don't need to transfer any data and bandwidth is an issue, but you will need the admin on the remote server to launch your .sql script. Here's what I do: Select all your user tables, right-click and copy. Paste them into the Query Analyzer and boom you've got the script to create your tables. Same with SPROC's and any other objects you have created. You can paste them all into one file.

Add this to the top of your SQL Script if the DB does not exist on the server:

CREATE DATABASE [YourDBName]--Skip these two lines if the DB does exist
Go
USE YourDBName -- Add this regardless

and Save it. The script can now be executed from the QA on any SQL server to create your DB.
Piece-o-cake.

If you did need to transfer any data, you could create a DTS package to export the data to the remote server. But of course you have to have access and it depends on the amount of data you have stored and bandwidth as to how effective a transfer would be.

But again, all of the solutions provided here by the other posters so far are viable. It's a matter of personal preference. All though, if you script it and something blows up locally, you can easily recreate your database without having to copy it back from the remote server. An unlikey scenario, but entirely possible... just a thought.

Good luck.

Copy to SQL2000 made command slow?

Hi,
I took a copy of a SQL7-Database with DTS to an SQL2000 Server.
The new copy of the SQL2000 runns fine. Also the application useing the new
DB.
But some of the SQL Commands became very solw (<1s => >200s).
Is it neceserry to optimize all the SQL-Commands - what e horror.
Thanks a lot
Andreas
Here the commands and the tabledef:
SELECT AG.GuidID, AG.Bezeichnung1 FROM
(Artikel A INNER JOIN StrukturArtikel SA ON A.GuidID = SA.ArtikelID)
INNER JOIN Attributgruppen AG ON A.AttributGruppenID = AG.GuidID
GROUP BY AG.GuidID, AG.Bezeichnung1, SA.StrukturKatalogID
HAVING SA.StrukturKatalogID = '5269C032AB1011D787D20000CB531E4D' ORDER BY
AG.Bezeichnung1
=== Aufbau Tabelle A======== CREATE TABLE [dbo].[Artikel] (
[GuidID] [char] (32) NOT NULL ,
[ArtikelNr] [varchar] (32) NULL ,
[AttributGruppenId] [char] (32) NULL ,
[VariationsAttribut] [char] (32) NULL ,
[Varianz] [varchar] (50) NULL ,
[Bezeichnung1] [varchar] (80) NULL ,
[BeschreibungLang1] [text] NULL ,
[VariationsBlockID] [char] (32) NULL ,
[Bezeichnung2] [varchar] (80) NULL ,
[Bezeichnung3] [varchar] (80) NULL ,
[Bezeichnung4] [varchar] (80) NULL ,
[Bezeichnung5] [varchar] (80) NULL ,
[BeschreibungLang2] [text] NULL ,
[BeschreibungLang3] [text] NULL ,
[BeschreibungLang4] [text] NULL ,
[BeschreibungLang5] [text] NULL ,
[anlage_datum] [datetime] NULL ,
[anlage_zeichen] [char] (16) NULL ,
[aender_datum] [datetime] NULL ,
[aender_zeichen] [char] (16) NULL ,
[ArtikelNrLief1] [varchar] (32) NULL ,
[Lieferant1] [varchar] (8) NULL ,
[ArtikelNrLief2] [varchar] (32) NULL ,
[Lieferant2] [varchar] (8) NULL ,
[ArtikelNrLief3] [varchar] (32) NULL ,
[Lieferant3] [varchar] (8) NULL ,
[Freigabe1] [tinyint] NULL ,
[Freigabe2] [tinyint] NULL ,
[Freigabe3] [tinyint] NULL ,
[Tag1] [varchar] (16) NULL ,
[Tag2] [varchar] (16) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE INDEX [ArtikelNr] ON [dbo].[Artikel]([ArtikelNr]) WITH FILLFACTOR
= 90 ON [PRIMARY]
CREATE INDEX [VariationsAttribut] ON [dbo].[Artikel]([VariationsAttribut])
WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [AttributGruppenId] ON [dbo].[Artikel]([AttributGruppenId])
WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [ArtikelNrLief1] ON [dbo].[Artikel]([ArtikelNrLief1]) WITH
FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [ArtikelNrLief2] ON [dbo].[Artikel]([ArtikelNrLief2]) WITH
FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [ArtikelNrLief3] ON [dbo].[Artikel]([ArtikelNrLief3]) WITH
FILLFACTOR = 90 ON [PRIMARY]
=== Aufbau Tabelle AG======== CREATE TABLE [dbo].[AttributGruppen] (
[GuidID] [char] (32) NOT NULL ,
[Reihenfolge] [int] NULL ,
[Ebene] [int] NULL ,
[Bezeichnung1] [varchar] (1024) NULL ,
[Bezeichnung2] [varchar] (1024) NULL ,
[Bezeichnung3] [varchar] (1024) NULL ,
[Bezeichnung4] [varchar] (1024) NULL ,
[Bezeichnung5] [varchar] (1024) NULL ,
[anlage_datum] [datetime] NULL ,
[anlage_zeichen] [char] (16) NULL ,
[aender_datum] [datetime] NULL ,
[aender_zeichen] [char] (16) NULL ,
[AttributGruppenNummer] [varchar] (15) NULL
) ON [PRIMARY]
CREATE INDEX [Ebene] ON [dbo].[AttributGruppen]([Ebene]) WITH FILLFACTOR
= 90 ON [PRIMARY]
=== Aufbau Tabelle SA======== CREATE TABLE [dbo].[StrukturArtikel] (
[StrukturID] [char] (32) NOT NULL ,
[StrukturKatalogID] [char] (32) NOT NULL ,
[ArtikelID] [char] (32) NOT NULL ,
[Katalogspezifisch] [bit] NULL ,
[Reihenfolge] [int] NULL ,
[Parent] [char] (32) NULL ,
[Ebene] [int] NULL ,
[anlage_datum] [datetime] NULL ,
[anlage_zeichen] [char] (16) NULL ,
[aender_datum] [datetime] NULL ,
[aender_zeichen] [char] (16) NULL
) ON [PRIMARY]
CREATE INDEX [ArtikelID] ON [dbo].[StrukturArtikel]([ArtikelID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [Parent] ON [dbo].[StrukturArtikel]([Parent]) WITH
FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [StrukturID] ON [dbo].[StrukturArtikel]([StrukturID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [StrukturKatalogID] ON
[dbo].[StrukturArtikel]([StrukturKatalogID]) WITH FILLFACTOR = 90 ON
[PRIMARY]Did you update stats after copying everything over? When
you say new application, you mean it was only converted
for SQL2K? or are there logic changes also? Compare
that all indexing is carried over, if it was supposed to
be!?! Beyond that, give SQL Profiler a shot to see the
exact slow statements and take it from there... Bruce
>--Original Message--
>Hi,
>I took a copy of a SQL7-Database with DTS to an SQL2000
Server.
>The new copy of the SQL2000 runns fine. Also the
application useing the new
>DB.
>But some of the SQL Commands became very solw (<1s =>
>200s).
>Is it neceserry to optimize all the SQL-Commands - what
e horror.
>Thanks a lot
>Andreas
>
>Here the commands and the tabledef:
>SELECT AG.GuidID, AG.Bezeichnung1 FROM
>(Artikel A INNER JOIN StrukturArtikel SA ON A.GuidID =SA.ArtikelID)
>INNER JOIN Attributgruppen AG ON A.AttributGruppenID =AG.GuidID
>GROUP BY AG.GuidID, AG.Bezeichnung1, SA.StrukturKatalogID
>HAVING SA.StrukturKatalogID
= '5269C032AB1011D787D20000CB531E4D' ORDER BY
>AG.Bezeichnung1
>
>=== Aufbau Tabelle A========>CREATE TABLE [dbo].[Artikel] (
> [GuidID] [char] (32) NOT NULL ,
> [ArtikelNr] [varchar] (32) NULL ,
> [AttributGruppenId] [char] (32) NULL ,
> [VariationsAttribut] [char] (32) NULL ,
> [Varianz] [varchar] (50) NULL ,
> [Bezeichnung1] [varchar] (80) NULL ,
> [BeschreibungLang1] [text] NULL ,
> [VariationsBlockID] [char] (32) NULL ,
> [Bezeichnung2] [varchar] (80) NULL ,
> [Bezeichnung3] [varchar] (80) NULL ,
> [Bezeichnung4] [varchar] (80) NULL ,
> [Bezeichnung5] [varchar] (80) NULL ,
> [BeschreibungLang2] [text] NULL ,
> [BeschreibungLang3] [text] NULL ,
> [BeschreibungLang4] [text] NULL ,
> [BeschreibungLang5] [text] NULL ,
> [anlage_datum] [datetime] NULL ,
> [anlage_zeichen] [char] (16) NULL ,
> [aender_datum] [datetime] NULL ,
> [aender_zeichen] [char] (16) NULL ,
> [ArtikelNrLief1] [varchar] (32) NULL ,
> [Lieferant1] [varchar] (8) NULL ,
> [ArtikelNrLief2] [varchar] (32) NULL ,
> [Lieferant2] [varchar] (8) NULL ,
> [ArtikelNrLief3] [varchar] (32) NULL ,
> [Lieferant3] [varchar] (8) NULL ,
> [Freigabe1] [tinyint] NULL ,
> [Freigabe2] [tinyint] NULL ,
> [Freigabe3] [tinyint] NULL ,
> [Tag1] [varchar] (16) NULL ,
> [Tag2] [varchar] (16) NULL
>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> CREATE INDEX [ArtikelNr] ON [dbo].[Artikel]
([ArtikelNr]) WITH FILLFACTOR
>= 90 ON [PRIMARY]
> CREATE INDEX [VariationsAttribut] ON [dbo].[Artikel]
([VariationsAttribut])
>WITH FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [AttributGruppenId] ON [dbo].[Artikel]
([AttributGruppenId])
>WITH FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [ArtikelNrLief1] ON [dbo].[Artikel]
([ArtikelNrLief1]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [ArtikelNrLief2] ON [dbo].[Artikel]
([ArtikelNrLief2]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [ArtikelNrLief3] ON [dbo].[Artikel]
([ArtikelNrLief3]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
>
>=== Aufbau Tabelle AG========>CREATE TABLE [dbo].[AttributGruppen] (
> [GuidID] [char] (32) NOT NULL ,
> [Reihenfolge] [int] NULL ,
> [Ebene] [int] NULL ,
> [Bezeichnung1] [varchar] (1024) NULL ,
> [Bezeichnung2] [varchar] (1024) NULL ,
> [Bezeichnung3] [varchar] (1024) NULL ,
> [Bezeichnung4] [varchar] (1024) NULL ,
> [Bezeichnung5] [varchar] (1024) NULL ,
> [anlage_datum] [datetime] NULL ,
> [anlage_zeichen] [char] (16) NULL ,
> [aender_datum] [datetime] NULL ,
> [aender_zeichen] [char] (16) NULL ,
> [AttributGruppenNummer] [varchar] (15) NULL
>) ON [PRIMARY]
> CREATE INDEX [Ebene] ON [dbo].[AttributGruppen]
([Ebene]) WITH FILLFACTOR
>= 90 ON [PRIMARY]
>
>=== Aufbau Tabelle SA========>CREATE TABLE [dbo].[StrukturArtikel] (
> [StrukturID] [char] (32) NOT NULL ,
> [StrukturKatalogID] [char] (32) NOT NULL ,
> [ArtikelID] [char] (32) NOT NULL ,
> [Katalogspezifisch] [bit] NULL ,
> [Reihenfolge] [int] NULL ,
> [Parent] [char] (32) NULL ,
> [Ebene] [int] NULL ,
> [anlage_datum] [datetime] NULL ,
> [anlage_zeichen] [char] (16) NULL ,
> [aender_datum] [datetime] NULL ,
> [aender_zeichen] [char] (16) NULL
>) ON [PRIMARY]
> CREATE INDEX [ArtikelID] ON [dbo].[StrukturArtikel]
([ArtikelID]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [Parent] ON [dbo].[StrukturArtikel]
([Parent]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [StrukturID] ON [dbo].[StrukturArtikel]
([StrukturID]) WITH
>FILLFACTOR = 90 ON [PRIMARY]
> CREATE INDEX [StrukturKatalogID] ON
>[dbo].[StrukturArtikel]([StrukturKatalogID]) WITH
FILLFACTOR = 90 ON
>[PRIMARY]
>
>.
>

Copy Selected column data from table to another during Upgrade of App

Hi,
I need to write a script that will be called during the database upgrade of my application. This is part of reorg of the tables. The script has to get data for say 4 columns from table A and insert it into another table B. Table B has identity insert column and remaining 4 columns matching the ones to be copied. The data is dependent on user database, hence number of records needs to be copied might be different. Also the columns can have null values.

I tried using bcp Command as follows..
bcp "select colA,colB,colC,colD from A" queryout "c:\temp\A.dat" -t"\t" -r"\n" -c

I'm able to get the dat file, but not the format file. Can anyone tell me how to get it using query file with -c option. Also if there is better option to copy data, kindly let me know.

This is very critical. Appreciate your help.

Thanks,
Ramya.Why not copy the data into a local temporary (or permanent) table?

I presume (perhaps incorrectly) that you want to retain this data to be inserted back into the modified table (or another table) later during the upgrade process.

Regards,

hmscott|||That's correct. I want to retain the data in Table A maybe delete a column after data copy. And i also want table B to have the values. Can you suggest any way to accomplish this..

Thanks,
Ramya.|||Create TableTemp (
ID int IDENTITY(1,1),
ColumnA varchar(10),
ColumnB varchar(10),
ColumnC varchar(10),
ColumnD varchar(10)
)
GO

INSERT INTO TableTemp (ColumnA, ColumnB, ColumnC, ColumnD)
SELECT ColumnA, ColumnB, ColumnC, ColumnD
FROM
MySourceTable
GO

ALTER TABLE MySourceTable DROP COLUMN ColumnA
GO

This will leave a permanent copy of the data from MySourceTable in TempTable.

Regards,

hmscott

That's correct. I want to retain the data in Table A maybe delete a column after data copy. And i also want table B to have the values. Can you suggest any way to accomplish this..

Thanks,
Ramya.