Sql Create error: 2012

Thursday, March 29, 2012

Create tables in SQL2K from XSD schema?

I am in need of some help!
Relatively new to the whole XML thing as far as SQL server goes...
I have an XSD schema that describes a bunch of tables with various
relationships. Is there a way for me to create tables and relationships based
on the XSD file.
I have tried the net but seem to be unable to find anything. Any help would
be greatly appreciated.
Matt
If at first you don't succeed... Hide the evidence that you tried!
In theory yes. Please see the link
http://support.microsoft.com/default...;en-us;316005. You have to
set SchemaGen property SQLXMLBulkLoad to true. BTW creating a schema from
hand is a very daunting task and I never able to make it work for complex
shcemas. Are you using some tool to generate the XSD shcema annotaion?
"Director - Minvent" wrote:

> I am in need of some help!
> Relatively new to the whole XML thing as far as SQL server goes...
> I have an XSD schema that describes a bunch of tables with various
> relationships. Is there a way for me to create tables and relationships based
> on the XSD file.
> I have tried the net but seem to be unable to find anything. Any help would
> be greatly appreciated.
> Matt
> --
> If at first you don't succeed... Hide the evidence that you tried!
|||The schema was written using XMLspy by one of the guys at Agilent
technologies (HP) as part of one of their products... Unfortunately it is not
intended for use and therefore is not supported, but hopefully tested prior
to release!
Basically it is the definition of an xml output file from an analytical
instrument which I want to capture data from. Hence trying to create a
database based on its definition.
I'm using VB.net and desperately trying to find a namespace containing
sqlxml?!? I am probably being really stupid... which wouldn't suprise me!.
Cheers
"Rashid" wrote:
[vbcol=seagreen]
> In theory yes. Please see the link
> http://support.microsoft.com/default...;en-us;316005. You have to
> set SchemaGen property SQLXMLBulkLoad to true. BTW creating a schema from
> hand is a very daunting task and I never able to make it work for complex
> shcemas. Are you using some tool to generate the XSD shcema annotaion?
> "Director - Minvent" wrote:

Create tables from master table

Hello all, I am looking for some help with the following:

I need to create individual tables from a master table, all columns will be copied and the selection is based on the values within one column - I need to create as many tables as there are distinct values in this column (there are around 60 distinct values).

I have a PL/SQL command to do this but this doesn't work in Transact-SQL, can this be converted to work? Is there a convert facility somewhere for this?

The PL/SQL command I have is:

set serveroutput on size 1000000

declare
cursor c_dept is
select distinct deptno
from emp;

v_sql varchar2(1000);
begin
for r_dept in c_dept loop
dbms_output.put_line ('dept: '||r_dept.deptno);

v_sql := 'create table kl_'||r_dept.deptno||
' as (select * from emp where deptno = '''||r_dept.deptno||''')';

dbms_output.put_line(v_sql);

execute immediate v_sql;
end loop; -- dept
end;
/

Any help would be greatly appreciated.

In sql server also you can do this,

Code Snippet

Create Table Emp

(

EmpId int,

DeptName varchar(10)

)

Insert Into Emp Values(1,'Sales')

Insert Into Emp Values(2,'Sales')

Insert Into Emp Values(3,'Sales')

Insert Into Emp Values(4,'Finance')

Insert Into Emp Values(5,'IT')

Code Snippet

--SQL Server 2005

Declare @.SQL as Varchar(max);

Declare @.PreparedSQL as Varchar(8000);

Set @.PreparedSQL=

'If exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[kl_?]'') and OBJECTPROPERTY(id, N''IsTable'') = 1)

Drop Table [dbo].[kl_?];

Select * Into [kl_?] From Emp Where Deptname=''?'';'

Set @.SQL=''

Select @.SQL = @.SQL + Replace(@.PreparedSQL,'?',deptname) From

(

Select Distinct Deptname from Emp

) as Depts

Exec (@.SQL);

Code Snippet

--SQL Server 2000

Declare @.Depts table (RowId int Identity(1,1), Deptname varchar(10));

Declare @.SQL as Varchar(8000);

Declare @.PreparedSQL as Varchar(8000);

Declare @.I as int;

Set @.PreparedSQL=

'If exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[kl_?]'') and OBJECTPROPERTY(id, N''IsTable'') = 1)

Drop Table [dbo].[kl_?]

Select * Into [kl_?] From Emp Where Deptname=''?'';'

Insert into @.Depts

Select Distinct Deptname From Emp;

Select @.I = 1;

While Exists(Select 1 From @.Depts Where RowId=@.I)

Begin

Select @.SQL = Replace(@.PreparedSQL,'?',deptname)

From @.Depts

Where Rowid=@.I;

--Print(@.SQL);

Exec(@.SQL)

Set @.I=@.I+1;

End

You can utilize the Indexed Views also. See Indexed views on Books Online.

|||Absolute star, thanks very much. Problem solved.

Create Tables Dynamically

Hi! I am using VS 2003 (v 1.1)

I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net.

How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation.

Give me the complete code with can example (if possible)

Thanks & Regrads

Jai Shankar

Here's anexample of how to create dynamic controls and add events. You can apply this to any type of control you need (in you case a Table).

create tables and insert data in sql server mobile on dekstop

Hello (sorry my bad english, im brazilian)

I was using Visual Studio 2003 and SQL Server CE 2.0 for C# mobile applications. The .sdf database were created in the emulator or in the mobile device itself using Query Analizer.

The application developed need some initial data to run, and this data is obtained executing one service that reads a postgree database, and insert the data in the SQL CE database of the mobile device. But, given the size of the database (maybe 10.000 rows), it tooks too much time (sometimes 6 hours).

Now we are migrating to Visual Studio 2005 and SQL Server 2005 Mobile Edition.

I want to know if its possible to create the .sdf database and load the data into this database on the desktop. Maybe through the execution of a .sql script, or through a service executed on the desktop.

After this, its just upload de .sdf file to the mobile device.

Thanks

Robson

Yes, you can create and populate your SQL Mobile database on the desktop as long as that desktop or server meets one of these criteria:

1. it contains a licensed copy of Visual Studio 2005

2. it contains a licesed copy of SQL Server 2005

3. it runs Windows XP Tablet PC edition

The code to do so is covered in the SQL Mobile Books Online.

There are other approaches as well, including third party tools like those at www.primeworks.pt, using SQL Server 2005 Integration Services, or creating and populating the database within SQL Server 2005 management studio.

-Darren

|||

Daren,

thanks for the help... I have found the way to create a sql server mobile 2005 database and insert data on the desktop using c# (running on desktop off course) at these forum topics:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=213973&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

I′ve used my licensed copy of Visual Studio 2005 to do it. Now I′ll test the solution to make a benchmark... I hope that now i will be able to create the database for my application faster....

thanks again...

|||

Please let us know how it works out for you.

Darren

|||

I have created the following table ('cidade' means city in portuguese):

CREATE TABLE cidade ( idcidade numeric(18,0) NOT NULL, codigo integer NOT NULL, descricao nvarchar(80) NOT NULL, ddd nvarchar(3), naturalidade nvarchar(80), idunidadefederativa numeric(18,0) NOT NULL )

The program (written in c# with visual studio 2005 and sql server 2005 mobile edition) insert 5565 rows in this table. It reads a sql insert line from a text file and execute the sql, eg:

INSERT INTO cidade VALUES (1, 1, 'ALTA FLORESTA D OESTE', NULL, NULL, 21)

It tooks 6 seconds to do it (running on a HP notebook with celeron processor).So, 927.5 rows per second.

Before, when we insert data on a database located at a pocket pc, this operation took 20 - 30 minutes (using c# compact framework from visual studio 2003 and sql ce 2.0).

|||

thanks for sharing your benchmark results - that's very good news.

-Darren

sql

Create table?

Hi All
I want to create a table with a name as a varible. That variable contains the table name by doing some string operations.
say: create table @.var
@.var contains the table name that is generated.
how could i do this?
plz help me .You will need to use dynamic sql to build your CREATE TABLE command, and then EXEC your dynamic sql.|||Why on earth would you ever want to do that? You are opening yourself up for a complete world of hurt. Whatever you are hoping to accomplish with this, you are most certainly solving the problem completely wrong.
Instead of asking fora horrible hack, you need to ask for advise on how to come up with an effective solution.|||Thanks a lot......... it solved my query.

CREATE TABLE/VIEW from stored procedure or SELECT...

Can anyone tell me how can I create a table in (SQL Server 2000) direct from a stored procedure execution or from a SELECT result?

I need something like this: CREATE TABLE < t > FROM <sp_name p1, p2, ...>or like this:

CREATE TABLE < t > FROM SELECT id, name FROM < w > ...

Thank you!

Look at SELECT ... INTO command.|||

use northwind

select * into #tablex from employees

select * from #tablex

|||

Sorry joeydj but your example create a copy of another table! I need to create a table that contains only a few columns from another table, so that why I need to use a SELECT or a stored procedure that build and execute a SELECT.

Can I do that?

Thanks!

|||Sorry gavrilenko_s but I miss your post! You are right! That is the solution! Thanks!|||

hi,

first you have to create a table that has a similar

structure with the Sp

and then you can use

insert into temp

exec sp1

here's a sample snippet

USE NORTHWIND
select 'my name.........................12345' as productname, 10000.00000
as unitprice, 10000.0000 as quantiTY,
10000.0000 as discount, 10000.0000 as extendedprice
into tempx

truncate table tempx

insert into tempx
exec dbo.CustOrdersDetail '10248'

select * from tempx
drop table tempx

also suggest you make use of UDFs

cheers :)

Create table,fields come from csv file

I want to create a table automatically,and fields come from a csv file

any idea? TIA

One way is using two packages and a configuration file.

I'm assuming that you are constructing the name of the table on the fly. Note that the table MUST always have the same format.

First create a sample of what you want your table to look like.

Package 1:
Create an SSIS package that loads data from a CSV file into that table.
Make the tablename come from a variable.
Put the variable in a configuration table

Package 2:
Create a variable that will contain the table name.
Create a variable expressions that has the sql to create the table using the variable previously defined.
Create a variable expression that has the sql to update the variable in the configuration table from Package 1
Create a SQL Task to create the table
Create a SQL Task to update the configuration table variable
Create an Execute SSIS package task to execute Package 1

Similarly you can extend this to the source by scanning directories for files and loading different CSV files into different tables.

Hope this helps,

Larry

Create table, table name as procedure parameter ?

Hi,

Is it possible to create a table in a stored procedure, where the table name

comes as a string procedure parameter?

Sorry, I am a newbie, maybe it is not possible this way,

but then what is the suggested way?

this results error in SQL Management Studio, if I press Parse.

>Incorrect syntax near '@.tableName'.

the "CREATE TABLE MyFixNameTable" line works, but it fixes the table name.

Code Snippet

CREATE PROCEDURE CreateMyTable

-- Add the parameters for the stored procedure here

@.tableName nvarchar(MAX) = ''

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- CREATE TABLE MyFixNameTable

CREATE TABLE @.tableName

(

"^First Name" varchar(25) NOT NULL,

"^Last Name" varchar(25) NOT NULL

)

END

You can't supply an object name as a variable/parameter to a SQL statement.

However, you could create the entire SQL statement as a string, and then use sp_executesql to execute that string.

You may find this article useful:

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

Like Arnie saie, you cannot create a table like this. Generally speaking, it is rarely a good thing to be programatically creating permanent tables to start with. You can do this with dynamic sql, but why? If you are going to load the data with the results of a query, it is likely best for you to do something like:

select firstName, lastName
into yourTableName
from ...

It is usually faster and avoids some logging overhead. The best way to do this is usually to have a permanent table that includes some other column to denote when you searched for data, etc, some discriminator. Then you can work with the data in the same tables every time you do this, and you code is simplified, and the data is available more readily for reporting what is being done.

Create table, remove records?

SQL 2000;
I have a table that has info from 2001 thru 2005, I want to create new
tables and move records from the original table to the new tables, based on
year.
What is the best way to do this?
1. Create empty tables for 2001 to 2005 with record structures identical
to the original and no indexes.
2. For each table, do something like the following:
insert into SALES_2001 select * from SALES where period = 2001
3. Create indexes to the new tables.
Since you are inserting into new tables, you could also temporarily set the
database recovery model to "simple". If there are millions of rows, this
will reduce transaction logging and speed things up.
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:1439F5CF-1C3E-4A28-BEF8-D7CA4423CBA3@.microsoft.com...
> SQL 2000;
> I have a table that has info from 2001 thru 2005, I want to create new
> tables and move records from the original table to the new tables, based
on
> year.
> What is the best way to do this?
>
|||Same advice JT gave you except if the table structures are the same use
select into instead of create table and insert into.
"JT" <someone@.microsoft.com> wrote in message
news:OxqloKwgFHA.3616@.TK2MSFTNGP12.phx.gbl...
> 1. Create empty tables for 2001 to 2005 with record structures
> identical
> to the original and no indexes.
> 2. For each table, do something like the following:
> insert into SALES_2001 select * from SALES where period = 2001
> 3. Create indexes to the new tables.
> Since you are inserting into new tables, you could also temporarily set
> the
> database recovery model to "simple". If there are millions of rows, this
> will reduce transaction logging and speed things up.
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:1439F5CF-1C3E-4A28-BEF8-D7CA4423CBA3@.microsoft.com...
> on
>
sql

Create table, remove records?

SQL 2000;
I have a table that has info from 2001 thru 2005, I want to create new
tables and move records from the original table to the new tables, based on
year.
What is the best way to do this?1. Create empty tables for 2001 to 2005 with record structures identical
to the original and no indexes.
2. For each table, do something like the following:
insert into SALES_2001 select * from SALES where period = 2001
3. Create indexes to the new tables.
Since you are inserting into new tables, you could also temporarily set the
database recovery model to "simple". If there are millions of rows, this
will reduce transaction logging and speed things up.
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:1439F5CF-1C3E-4A28-BEF8-D7CA4423CBA3@.microsoft.com...
> SQL 2000;
> I have a table that has info from 2001 thru 2005, I want to create new
> tables and move records from the original table to the new tables, based
on
> year.
> What is the best way to do this?
>|||Same advice JT gave you except if the table structures are the same use
select into instead of create table and insert into.
"JT" <someone@.microsoft.com> wrote in message
news:OxqloKwgFHA.3616@.TK2MSFTNGP12.phx.gbl...
> 1. Create empty tables for 2001 to 2005 with record structures
> identical
> to the original and no indexes.
> 2. For each table, do something like the following:
> insert into SALES_2001 select * from SALES where period = 2001
> 3. Create indexes to the new tables.
> Since you are inserting into new tables, you could also temporarily set
> the
> database recovery model to "simple". If there are millions of rows, this
> will reduce transaction logging and speed things up.
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:1439F5CF-1C3E-4A28-BEF8-D7CA4423CBA3@.microsoft.com...
> on
>

Create table, concatenate data

HTH,
John Scragg
"Steve1445" wrote:

> I am trying to concatenate four fields from one table: org.address1,
> org.city, org.state, org.zip with at least four fields from another table:
> url.http_start, url.plus_sign, url.tail and url.http_end in a trigger that
> would fire whenever a new record was created. The end stored result would be
> an address used for Yahoo maps and would look like this:
> http://maps.yahoo.com/py/maps.py?Pyt...et+Map=Get+Map
> The data in the url table would have this data in it and really should just
> be one row used over and over again.
> url.http_start = http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=
> url.plus_sign = +
> url.http_tail = &csz=
> url.http_end = &Get+Map=Get+Map
> I've come at this from a few different angles but I'm afraid my skills are
> not up to what I am looking for; I would appreciate anyone's input.
>
|||Thanks for your reply, I was having troubles with the + sign in the final
concatenate being seen as something other than just what it is, a plus sign.
So I figured I would just store the actual plus sign in the plus_sign field
to use in the final concatenation. Thanks for your help. Steve
"John Scragg" wrote:
[vbcol=seagreen]
> I dont't unserstand the "plus_sign" thing, but I think you want something
> like this. Since the tables are not related, you cannot really join on them
> and you most likely want to store the "http" values in local variables:
> DECLARE @.start varchar (100)
> DECLARE @.plus char(1)
> DECLARE @.tail varchar(100)
> DECLARE @.end varchar(100)
> SELECT @.start = http_start, @.plus = plus_sign, @.tail = http_tail, @.end =
> http_end
> FROM HTTP_TABLE
> SELECT@.start + org.address1 + @.tail + org.city + @.plus + org.state + @.plus +
> @.plus + org.zip + @.end
> FROM ORG_TABLE org
> Like I said, not sure what you wer going for with the "plus_sign" column,
> but it looks like that might get you going on it

> HTH,
> John Scragg
> "Steve1445" wrote:

Create table, concatenate data

HTH,
John Scragg
"Steve1445" wrote:

> I am trying to concatenate four fields from one table: org.address1,
> org.city, org.state, org.zip with at least four fields from another table:
> url.http_start, url.plus_sign, url.tail and url.http_end in a trigger that
> would fire whenever a new record was created. The end stored result would
be
> an address used for Yahoo maps and would look like this:
> http://maps.yahoo.com/py/maps.py?Py...Get+Map=Get+Map
> The data in the url table would have this data in it and really should jus
t
> be one row used over and over again.
> url.http_start = http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=
> url.plus_sign = +
> url.http_tail = &csz=
> url.http_end = &Get+Map=Get+Map
> I've come at this from a few different angles but I'm afraid my skills are
> not up to what I am looking for; I would appreciate anyone's input.
>|||Thanks for your reply, I was having troubles with the + sign in the final
concatenate being seen as something other than just what it is, a plus sign.
So I figured I would just store the actual plus sign in the plus_sign field
to use in the final concatenation. Thanks for your help. Steve
"John Scragg" wrote:
[vbcol=seagreen]
> I dont't unserstand the "plus_sign" thing, but I think you want something
> like this. Since the tables are not related, you cannot really join on the
m
> and you most likely want to store the "http" values in local variables:
> DECLARE @.start varchar (100)
> DECLARE @.plus char(1)
> DECLARE @.tail varchar(100)
> DECLARE @.end varchar(100)
> SELECT @.start = http_start, @.plus = plus_sign, @.tail = http_tail, @.end =
> http_end
> FROM HTTP_TABLE
> SELECT@.start + org.address1 + @.tail + org.city + @.plus + org.state + @.plus
+
> @.plus + org.zip + @.end
> FROM ORG_TABLE org
> Like I said, not sure what you wer going for with the "plus_sign" column,
> but it looks like that might get you going on it

> HTH,
> John Scragg
> "Steve1445" wrote:
>

Create table, concatenate data

I am trying to concatenate four fields from one table: org.address1,
org.city, org.state, org.zip with at least four fields from another table:
url.http_start, url.plus_sign, url.tail and url.http_end in a trigger that
would fire whenever a new record was created. The end stored result would be
an address used for Yahoo maps and would look like this:
http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=123+Main+Street&csz=Anywhere+KA++09186&Get+Map=Get+Map
The data in the url table would have this data in it and really should just
be one row used over and over again.
url.http_start = http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr= url.plus_sign = +
url.http_tail = &csz= url.http_end = &Get+Map=Get+Map
I've come at this from a few different angles but I'm afraid my skills are
not up to what I am looking for; I would appreciate anyone's input.I dont't unserstand the "plus_sign" thing, but I think you want something
like this. Since the tables are not related, you cannot really join on them
and you most likely want to store the "http" values in local variables:
DECLARE @.start varchar (100)
DECLARE @.plus char(1)
DECLARE @.tail varchar(100)
DECLARE @.end varchar(100)
SELECT @.start = http_start, @.plus = plus_sign, @.tail = http_tail, @.end =http_end
FROM HTTP_TABLE
SELECT@.start + org.address1 + @.tail + org.city + @.plus + org.state + @.plus +
@.plus + org.zip + @.end
FROM ORG_TABLE org
Like I said, not sure what you wer going for with the "plus_sign" column,
but it looks like that might get you going on it :)
HTH,
John Scragg
"Steve1445" wrote:
> I am trying to concatenate four fields from one table: org.address1,
> org.city, org.state, org.zip with at least four fields from another table:
> url.http_start, url.plus_sign, url.tail and url.http_end in a trigger that
> would fire whenever a new record was created. The end stored result would be
> an address used for Yahoo maps and would look like this:
> http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=123+Main+Street&csz=Anywhere+KA++09186&Get+Map=Get+Map
> The data in the url table would have this data in it and really should just
> be one row used over and over again.
> url.http_start = http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=
> url.plus_sign = +
> url.http_tail = &csz=> url.http_end = &Get+Map=Get+Map
> I've come at this from a few different angles but I'm afraid my skills are
> not up to what I am looking for; I would appreciate anyone's input.
>|||Thanks for your reply, I was having troubles with the + sign in the final
concatenate being seen as something other than just what it is, a plus sign.
So I figured I would just store the actual plus sign in the plus_sign field
to use in the final concatenation. Thanks for your help. Steve
"John Scragg" wrote:
> I dont't unserstand the "plus_sign" thing, but I think you want something
> like this. Since the tables are not related, you cannot really join on them
> and you most likely want to store the "http" values in local variables:
> DECLARE @.start varchar (100)
> DECLARE @.plus char(1)
> DECLARE @.tail varchar(100)
> DECLARE @.end varchar(100)
> SELECT @.start = http_start, @.plus = plus_sign, @.tail = http_tail, @.end => http_end
> FROM HTTP_TABLE
> SELECT@.start + org.address1 + @.tail + org.city + @.plus + org.state + @.plus +
> @.plus + org.zip + @.end
> FROM ORG_TABLE org
> Like I said, not sure what you wer going for with the "plus_sign" column,
> but it looks like that might get you going on it :)
> HTH,
> John Scragg
> "Steve1445" wrote:
> > I am trying to concatenate four fields from one table: org.address1,
> > org.city, org.state, org.zip with at least four fields from another table:
> > url.http_start, url.plus_sign, url.tail and url.http_end in a trigger that
> > would fire whenever a new record was created. The end stored result would be
> > an address used for Yahoo maps and would look like this:
> >
> > http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=123+Main+Street&csz=Anywhere+KA++09186&Get+Map=Get+Map
> >
> > The data in the url table would have this data in it and really should just
> > be one row used over and over again.
> >
> > url.http_start = http://maps.yahoo.com/py/maps.py?Pyt=Tmap&addr=
> > url.plus_sign = +
> > url.http_tail = &csz=> > url.http_end = &Get+Map=Get+Map
> >
> > I've come at this from a few different angles but I'm afraid my skills are
> > not up to what I am looking for; I would appreciate anyone's input.
> >

create table(s) from xml schema

I'm using SQL 2005.

I have an XML schema that is maintained by an outside source and I'd like to use it to create the table structures. Is there a way to do this without manually creating the tables? I will be receiving data files that will need to be validated against the schema and then uploaded into a database.

I suppose I could just load the xml into a single field using the xml data type, but it seems like it may be easier to access the data if it was broken out.

Please let me know if anyone is aware of a tool that would automatically create the SQL tables from an XML schema.

Thanks,

John

You can use XML Bulk Load component to create your tables from xml schema file :

You can set the SchemaGen property to TRUE to create your tables.

Refer Books on line SQL Server 2005 :

SQL Server 2005 Books online ->

SQL Server Programming Reference ->

SQL XML 4.0 Programming ->

Performing Bulk Load of XML Data (SQLXML 4.0)

Refer the following links to know more about XmlBulkLoad component:

Performing Bulk Load of XML Data (SQLXML 4.0)

Using SQL Server's XML Support > XML Bulk Load

How to import XML into SQL Server with the XML Bulk Load component

Thanks

Naras.

sql

create table(s) from xml schema

I'm using SQL 2005.

I suppose I could just load the xml into a single field using the xml data type, but it seems like it may be easier to access the data if it was broken out.

Please let me know if anyone is aware of a tool that would automatically create the SQL tables from an XML schema.

Thanks,

John

You can use XML Bulk Load component to create your tables from xml schema file :

You can set the SchemaGen property to TRUE to create your tables.

Refer Books on line SQL Server 2005 :

SQL Server 2005 Books online ->

SQL Server Programming Reference ->

SQL XML 4.0 Programming ->

Performing Bulk Load of XML Data (SQLXML 4.0)

Refer the following links to know more about XmlBulkLoad component:

Performing Bulk Load of XML Data (SQLXML 4.0)

Using SQL Server's XML Support > XML Bulk Load

How to import XML into SQL Server with the XML Bulk Load component

Thanks

Naras.

CREATE TABLE without columns

Is it possible to use t-sql to Create a table without columns?
I keep getting syntax errors and can't figure out the correct syntax to do
this.
Thanks,
RSHWhat exactly is a table without columns? By definition, a table has at
least one column. What is your actual goal / business requirement?
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:uI0m1mWEGHA.3820@.TK2MSFTNGP12.phx.gbl...
> Is it possible to use t-sql to Create a table without columns?
> I keep getting syntax errors and can't figure out the correct syntax to do
> this.
> Thanks,
> RSH
>|||"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:uI0m1mWEGHA.3820@.TK2MSFTNGP12.phx.gbl...
> Is it possible to use t-sql to Create a table without columns?
> I keep getting syntax errors and can't figure out the correct syntax to do
> this.
> Thanks,
> RSH
>
No. A table has to have at least one column. Maybe if you explain what you
want to achieve we could help you with an alternative.
David Portas
SQL Server MVP
--|||No -- why would you want a table with no columns? That would also mean it
could not have any rows. What would you do with such a table?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:uI0m1mWEGHA.3820@.TK2MSFTNGP12.phx.gbl...
> Is it possible to use t-sql to Create a table without columns?
> I keep getting syntax errors and can't figure out the correct syntax to do
> this.
> Thanks,
> RSH
>|||a table by definition has columns
what would be the purpose of one without columns?
RSH wrote:
> Is it possible to use t-sql to Create a table without columns?
> I keep getting syntax errors and can't figure out the correct syntax to do
> this.
> Thanks,
> RSH
>

Create Table within an IF statement causes error

This doesn't make any sense to me. I am trying to create a stored procedure that creates a temp table using T-SQL. The table will be created differently depending on the arguments passed. Here is an example of what I am trying to do:

DECLARE @.Switch bit

SET @.Switch = 0

IF @.Switch = 0

BEGIN

PRINT @.Switch

CREATE TABLE #DontWork (Zero int)

END

ELSE

BEGIN

PRINT @.Switch

CREATE TABLE #DontWork (One int)

END

SELECT * FROM #DontWork

If you run this as is, it fails stating that "There is already an object named '#DontWork' in the database." However if you comment out one of the CREATE TABLE statements (either one of them), it works fine. The PRINT @.Switch line will prove that the IF ELSE statement is evaluating properly if you change the value of @.Switch. My guess is that the parsing engine is throwing the error before it even tries to run the code. Is there any way to make this work the way it should? Or do I have to resort to creating 2 different tables and modifying the rest of my code to compensate for the change?

This is usually caused because you have ran a CREATE TABLE statement in a previous development iteration. Try appending this to the end of your code:

Code Snippet

drop table #DontWork

The temp table stays in scope after you run through one time so the next time through you get the error. Try hiliting the code I've given you and execute just the DROP TABLE. Then un-hilite the code and rerun query. It should run correctly once you have dropped the table.

OK, I'm all wet... Hang on.

You can alter it:

Code Snippet

create table #what (one int)

alter table #what
add two int

alter table #what
drop column one

select * from #what

drop table #what

/*
two
--
*/

|||

The code doesn't execute.

The parsing engine is attempting to resolve the objects, and (incorrectly, in my opinion) assumes that the second instance of the create table is attempting to make a second object with the same name. The parsing engine is resolving objects, not checking logic and code flow.

To test, comment out EITHER CREATE statement and the code executes.

Your options include creating the #Temp table before the IF statement, or using a different #Table name in the second instance.

Or you could have both switched locations call out to another procedure that creates the #Temp table.

|||My code already involves altering the table. I was just trying to use an IF statement because one scenario creates a predictable table structure, and the other side requires that the field names be calculated at run time. I was trying to save myself some effort by simply having that CREATE TABLE command in there twice, but it seems that because of what Arnie said about the parsing engine resolving objects, not checking logic and code flow, I'm going to have to do things the complicated way. I wish there was a way to communicate things like this to the powers that be at Microsoft. Any idea how to do that, if at all possible?|||

They do pay attention to the suggestions.

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

|||MS SQL has always had a problem with this construct. The solution, as mentioned, is to create the table once, and then use alter table to change the table to what you want. Or just create 2 tables of different names.

|||

SQL Server compiles the entire batch (SP, trigger, function or ad-hoc) and compilation doesn't take into account run-time information (variable values, control of flow etc). This gets tricky for temporary tables because of the way they are scoped. For best performance and manageability, you should put the creation logic for the different conditions in their own SPs and the execution logic too. This provides better reusability. You could use the ALTER TABLE approach but that will give bad performance in SQL Server 2005 since it negates the caching that we do automatically on temporary tables (metadata & 1 page of allocation which can get reused). Of course, if you can remove the temporary tables altogether.

Btw, your code will work if you were creating a permanent table conditionally.

Create Table with variable name

This should be simple, but...

I want to create a table in a stored proc using a variable name instead of something hard coded. I was hoping to do something like....

CREATE PROCEDURE foo

-- Add the parameters for the stored procedure here

@.TableName char = null

BEGIN

SET NOCOUNT ON;

CREATE TABLE @.TableName (

[HRMONTH] [int] NULL,

[HRYEAR] [int] NULL

) ON [PRIMARY]

But no combination of names '@.'s, etc, allows me to use a variable name that I passed into the procedure. What am I missing? I will either receive a syntax error or the procedure will create a table called TableName rather than whatever TableName really stands for...

Thanks,

Tom

DECLARE @.ExecSQL NVARCHAR(300
SET @.ExecSQL = "CREATE TABLE @.TableName ..."
EXECUTE @.ExecSQL @.TableName

Remember that all variables have to be NVARCHAR and not VARCHAR. Also the exact syntax might be a bit off. In hat case use this as a reference. Hope this helps.|||

>>Remember that all variables have to be NVARCHAR and not VARCHAR

This is only true for sp_executesql, exec dynamic sql works with varchar also take a look at this example

declare @.table varchar(49),@.sql varchar(500)

select @.table ='Orders2006'
select @.sql = 'create table ' + @.table + '(id int)'
exec (@.sql)

exec('insert ' + @.table + ' values(1)')

exec('select * from ' + @.table)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Ahh. One thing to note about the above method is that I believe it may allows for more potentials for sql injections - may not be an issue with this but with queries and etc I believe it shoudl be avoided as opposed to the other method due to these security restrictions related to sql injection/execution.|||

There is always this

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

It deals with the whole thing, injections, permissions etc etc

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks for the responses. This worked well, until I read the article in the previous post. So, maybe this wasn't such a hot idea...

Thanks again,

Tom

|||

One way is to take below approach which doesn't require dynamic SQL:

create table _tmp (

...

)

exec sp_rename _tmp, @.name_passed_to_proc

Create Table with Unknown Table Name?

Hi, question:

I want to create a cursor that will loop through a table and find all the distinct county names for some address records. Then, it will create a new table with each of these county names as it loops through the cursor pulling each of the records associated with these records.

My question: How do you use the INTO syntax in Microsoft Access to create a new table when you don't know the name of the table you're creating until it finds it in the database?

My code thus far: (untested, so there might be some minor syntax errors)

DECLARE myCursor CURSOR FOR
SELECT DISTINCT CountyName FROM [ALL_RECORDS]

DECLARE @.UniqueCounty

OPEN myCursor

FETCH NEXT FROM myCursor INTO @.UniqueCounty

WHILE (@.@.FETCH_STATUS=0)
BEGIN
SELECT * FROM [ALL_RECORDS] INTO @.UniqueCounty /* <-- HERE IS THE PROBLEM!!! */
FETCH NEXT FROM myCursor INTO @.UniqueCounty
ENDAside from any questions of if or why you want to do this, you will need to use dynamic sql (aka string concatenation) to accomplish your goal.

Example:

create table #ALL_RECORDS (pk int primary key, CountyName varchar(128))
insert into #ALL_RECORDS
values (1,'del_1')
insert into #ALL_RECORDS
values (2,'del_2')
insert into #ALL_RECORDS
values (3,'insertion_attack] from (select ''Gotcha'' as val ) as tab_alias; select * from master.dbo.sysxlogins -- ')
Declare @.sql nvarchar(4000)
DECLARE @.UniqueCounty sysname
DECLARE myCursor CURSOR FOR
SELECT DISTINCT CountyName FROM #ALL_RECORDS

OPEN myCursor
FETCH NEXT FROM myCursor INTO @.UniqueCounty
WHILE (@.@.FETCH_STATUS=0)
BEGIN
set @.sql = '
SELECT * INTO [' + @.UniqueCounty + ']FROM #ALL_RECORDS '
exec (@.sql)
FETCH NEXT FROM myCursor INTO @.UniqueCounty
END

/*
Note that entry 3 in #all_records demonstrates one of the perils of this method, namely that your are executing a string the exact contents of which you do not know, leaving your system vulnerable to an insertion attack.
*/|||Thanks, I will try it.

The WHY is because I need smaller source tables refreshed every night from a new gigantic database that gets refreshed every night.

At least I have a starting point, thanks!sql

create table with TRIGGER

I need an Insert trigger to generate new tables when i insert a new record..
new tables will be "MasterSub_[ID]" where ID is the id of new inserted
parent record..
I also want a delete triger to remove child table if it has no data in it..
Any help plz'This is a very bad design and will likely perform poorly. Why do you need
to create tables every time you insert rows?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:uFTjXyR7FHA.1188@.TK2MSFTNGP12.phx.gbl...
>I need an Insert trigger to generate new tables when i insert a new
>record..
> new tables will be "MasterSub_[ID]" where ID is the id of new inserted
> parent record..
> I also want a delete triger to remove child table if it has no data in
> it..
> Any help plz'
>
>

create table with recursive relationship

I am fairly new to SQL and I am currently trying to create
a SQL table (using Microsoft SQL) that has a recursive
relationship, let me try to explain:

I have a piece of Data let's call it "Item" wich may again contain one
more "Items". Now how would I design a set of SQL Tables that are
capable of storing this information?

I tried the following two approaches:

1.) create a Table "Item" with Column "ItemID" as primary key, some
colums for the Data an Item can store and a Column "ParentItemID". I
set a foreign key for ParentItemID wich links to the primarykey
"ItemID" of the same table.

2.) create separate Table "Item_ParentItem" that stores
ItemID-ParentItemID-pairs. Each column has a foreign key linked to
primary key of the "Item" Column "ItemID".

In both approaches when I try to delete an Item I get an Exception
saying that the DELETE command could not be executed because it
violates a COLUMN REFERENCE constraint. The goal behind these FK_PK
relations is is that when an Item gets deleted, all childItems should
automatically be deleted recursively.

How is this "standard-problem" usually solved in sql? Or do I inned to
implement the recursive deletion myself using stored
procedures or something ?You can get away with the first approach. However, you may not use ON
DELETE CASCADE. Rather, you are looking at a trigger that can manage this.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"Robert Ludig" <schwertfischtrombose@.gmx.de> wrote in message
news:1147782885.171670.231170@.i40g2000cwc.googlegr oups.com...
I am fairly new to SQL and I am currently trying to create
a SQL table (using Microsoft SQL) that has a recursive
relationship, let me try to explain:

I have a piece of Data let's call it "Item" wich may again contain one
more "Items". Now how would I design a set of SQL Tables that are
capable of storing this information?

I tried the following two approaches:

2.) create separate Table "Item_ParentItem" that stores
ItemID-ParentItemID-pairs. Each column has a foreign key linked to
primary key of the "Item" Column "ItemID".

How is this "standard-problem" usually solved in sql? Or do I inned to
implement the recursive deletion myself using stored
procedures or something ?|||On 16 May 2006 05:34:45 -0700, Robert Ludig wrote:

>I am fairly new to SQL and I am currently trying to create
>a SQL table (using Microsoft SQL) that has a recursive
>relationship, let me try to explain:
>I have a piece of Data let's call it "Item" wich may again contain one
>more "Items". Now how would I design a set of SQL Tables that are
>capable of storing this information?
>
>I tried the following two approaches:
(snip)

Hi Robert,

I agree with Tom that the first approach is better than the first. But
there are also some radically different ways to store a recursive
relationship or hierarchy. One of the more popular variants is the
nested set model. It's not nearly as intuitive as the model you are
proposing, but it performs far superior in some scenario's.

Google for "Nested Set Model" if you want to know the details.

--
Hugo Kornelis, SQL Server MVP|||>> How is this "standard-problem" usually solved in sql? <<

Get a copy of TREES & HIERARCHIES IN SQL for several ways to model this
kind of problem.

>> Or do I inned to implement the recursive deletion myself using stored
procedures or something ? <<

No need for recursive procedural code if you use the nested sets model.
Younger programmers who learned HTML, XML, etc. find it to be
intuitive. Older programmers who grew up with pointer chains need to
adjust their mind-set.

Create table with PK on two columns

Hi,
Could you tell me the syntax to set primary key on two columns when I create
a table? I can't find it in the books. The syntax I found is
CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
How can put c2 as part of PRIMARY KEY?
I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me.
Thanks.Chrissi wrote:
> Hi,
> Could you tell me the syntax to set primary key on two columns when I
> create a table? I can't find it in the books. The syntax I found is
> CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
> How can put c2 as part of PRIMARY KEY?
> I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls
> tell me.
> Thanks.
Create table MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL
PRIMARY KEY (c1, c2) )
or
Create table MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL )
Alter Table MyTable
ADD PRIMARY KEY (c1, c2)
David Gugick
Imceda Software
www.imceda.com|||Create Table MyTable
(c1 INT Not Null,
c2 INT Not Null,
Primary Key (C1, c2))
"§Chrissi§" wrote:

> Hi,
> Could you tell me the syntax to set primary key on two columns when I crea
te
> a table? I can't find it in the books. The syntax I found is
> CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
> How can put c2 as part of PRIMARY KEY?
> I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me
.
> Thanks.
>
>|||CREATE TABLE MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL,
CONSTRAINT pk_MyTable PRIMARY KEY(c1 ,c2)
)
or
CREATE TABLE MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY(c1 ,c2)
)
It's technically a constraint in both cases, but you aren't
required to give it a name.
Steve Kass
Drew University
Chrissi wrote:

>Hi,
>Could you tell me the syntax to set primary key on two columns when I creat
e
>a table? I can't find it in the books. The syntax I found is
>CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
>How can put c2 as part of PRIMARY KEY?
>I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me.
>Thanks.
>
>|||Server: Msg 1911, Level 16, State 1, Line 1
Column name 'C1' does not exist in the target table.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Watch your spelling! Some of us choose a case-sensitive
collation now and then. ;)
SK
CBretana wrote:
>Create Table MyTable
> (c1 INT Not Null,
> c2 INT Not Null,
> Primary Key (C1, c2))
>"§Chrissi§" wrote:
>
>|||Oops! My typing is never good (two finger hint n pec) but I noticed the
upper case C and left it that way anyway... Out of curiousity, why are you
using case-sensitive collation?
"Steve Kass" wrote:

> Server: Msg 1911, Level 16, State 1, Line 1
> Column name 'C1' does not exist in the target table.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> Watch your spelling! Some of us choose a case-sensitive
> collation now and then. ;)
> SK
> CBretana wrote:
>
>|||
CBretana wrote:

> Oops! My typing is never good (two finger hint n pec) but I noticed the
> upper case C and left it that way anyway... Out of curiousity, why are you
> using case-sensitive collation?
Mostly so I can generate the appropriate error messages to include in posts
like this one. ;)
I didn't used to pay attention to this, and it didn't matter as much when
keypunch machines were uppercase-only, or with case-insensitive languages
like Pascal. I had to break sloppy habits when C came along, and though I
slipped into old habits when I started using SQL, I've found more and more
reasons not to be sloppy lately, such as keeping Erland from bugging me
if I put "northwind"."orders" in examples I post. ;)
There are plenty of things you can write that will behave differently
according to collation and language settings, and forcing myself to
be careful about case helps me see and avoid them.
SK
> "Steve Kass" wrote:
>|||>> Out of curiousity, why are you using case-sensitive collation? <<
Because Standard SQL is case-sensitive.

CREATE TABLE with multiple-column primary key?

Is it possible to issue the CREATE TABLE command and specify a multiple-colu
mn primary key?
If so, what is the syntax? I've checked BOL and as far as I can tell, you ma
y only select
a single column as the primary key *within the CREATE TABLE command*; ALTER
TABLE must be used
for multiple-column primary keys.
For example (this does *not* work):
CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix int
NOT NULL
PRIMARY KEY ProcedureID, ProcedureSuffix)
Thanks in advance --
CarlOnly a matter of a comma and a parenthesis. Below work fine:
CREATE TABLE #TEMPProcedures
(ProcedureID int NOT NULL
,ProcedureSuffix int NOT NULL
,PRIMARY KEY (ProcedureID, ProcedureSuffix))
I prefer to name all my contraints...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Carl Imthurn" <nospam@.all.com> wrote in message news:uIvG984GGHA.1032@.TK2MSFTNGP12.phx.gbl
..
> Is it possible to issue the CREATE TABLE command and specify a multiple-co
lumn primary key?
> If so, what is the syntax? I've checked BOL and as far as I can tell, you
may only select
> a single column as the primary key *within the CREATE TABLE command*; ALTE
R TABLE must be used
> for multiple-column primary keys.
> For example (this does *not* work):
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix in
t NOT NULL
> PRIMARY KEY ProcedureID, ProcedureSuffix)
> Thanks in advance --
> Carl
>|||Thanks Tibor - that worked.
And, your comment about naming all constraints is well taken.
Carl
Tibor Karaszi wrote:

> Only a matter of a comma and a parenthesis. Below work fine:
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL
> ,ProcedureSuffix int NOT NULL
> ,PRIMARY KEY (ProcedureID, ProcedureSuffix))
> I prefer to name all my contraints...

Create Table with Foreign Keys

How do I create a table with a foreign keyCREATE TABLE Parent ( ParentId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ColA INT)

CREATE TABLE Child(ChildId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ParentId INT NOT NULL CONSTRAINT FK_Child_Parent1 FOREIGN KEY REFERENCES Parent(ParentId))|||also a foreign key has the following options

FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]

FOREIGN KEY...REFERENCES

Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column(s) in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table.

ref_table

Is the name of the table referenced by the FOREIGN KEY constraint.

(ref_column[,...n])

Is a column, or list of columns, from the table referenced by the FOREIGN KEY constraint.

ON DELETE {CASCADE | NO ACTION}

Specifies what action takes place to a row in the table created, if that row has a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table. If NO ACTION is specified, SQL Server raises an error and the delete action on the row in the parent table is rolled back.

For example, in the Northwind database, the Orders table has a referential relationship with the Customers table. The Orders.CustomerID foreign key references the Customers.CustomerID primary key.

If a DELETE statement is executed on a row in the Customers table, and an ON DELETE CASCADE action is specified for Orders.CustomerID, SQL Server checks for one or more dependent rows in the Orders table. If any, the dependent rows in the Orders table are deleted, as well as the row referenced in the Customers table.

On the other hand, if NO ACTION is specified, SQL Server raises an error and rolls back the delete action on the Customers row if there is at least one row in the Orders table that references it.

ON UPDATE {CASCADE | NO ACTION}

Specifies what action takes place to a row in the table created, if that row has a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.

If CASCADE is specified, the row is updated in the referencing table if that row is updated in the parent table. If NO ACTION is specified, SQL Server raises an error and the update action on the row in the parent table is rolled back.

For example, in the Northwind database, the Orders table has a referential relationship with the Customers table: Orders.CustomerID foreign key references the Customers.CustomerID primary key.

If an UPDATE statement is executed on a row in the Customers table, and an ON UPDATE CASCADE action is specified for Orders.CustomerID, SQL Server checks for one or more dependent rows in the Orders table. If any exist, the dependent rows in the Orders table are updated, as well as the row referenced in the Customers.

Alternately, if NO ACTION is specified, SQL Server raises an error and rolls back the update action on the Customers row if there is at least one row in the Orders table that references it.

Also you should know
FOREIGN KEY Constraints
When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned.

FOREIGN KEY constraints are applied to the preceding column unless source columns are specified.

FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER.

FOREIGN KEY constraints can reference another column in the same table (a self-reference).

The REFERENCES clause of a column-level FOREIGN KEY constraint can list only one reference column, which must have the same data type as the column on which the constraint is defined.

The REFERENCES clause of a table-level FOREIGN KEY constraint must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.

CASCADE may not be specified if a column of type timestamp is part of either the foreign key or the referenced key.

It is possible to combine CASCADE and NO ACTION on tables that have referential relationships with each other. If SQL Server encounters NO ACTION, it terminates and rolls back related CASCADE actions. When a DELETE statement causes a combination of CASCADE and NO ACTION actions, all the CASCADE actions are applied before SQL Server checks for any NO ACTION.

A table can contain a maximum of 253 FOREIGN KEY constraints.

FOREIGN KEY constraints are not enforced on temporary tables.

A table can reference a maximum of 253 different tables in its FOREIGN KEY constraints.

FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE constraints in the referenced table or in a UNIQUE INDEX on the referenced table.|||also a foreign key has the following options

FOREIGN KEY
[ ( column [ ,...n ] ) ]
REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { CASCADE | NO ACTION } ]
[ ON UPDATE { CASCADE | NO ACTION } ]
..........
If you are using SQL Server 2005 you would do well to read the BoL entry as there are now more options than those above - more in line with the sql standard.|||Hopefully Nick won't be copying & Pasting the BOL entrys into this thread as well - lol

GW|||I am finishing up some handy dadndy little sql to find all of the foriegn keys without an index.

I need to find something better to do with my time.|||Hopefully Nick won't be copying & Pasting the BOL entrys into this thread as well - lol

GW

no i wont... i guess the previous one was enoughsql

Create table with Encrypted passwords

Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regards
This link will give you an overview of column level encryption:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regards
|||Hi,
The PWDENCRYPT and PWDCOMPARE functions are used to encrypt and compare DATA
passwords are not visible in anywhere in the database.
FYI, PWDENCRYPT and PWDCOMPARE are undocumented functions , so it can change
in future versions.
Sample code to create table and encypt the password column and usage:-
Create table users ( userid int identity (1,1) not null,
pswd varbinary (128))
-- INSERTING ENCRYPED value
-- hard coded string should be replace
-- by a text box value from screen
Insert into users values (PWDENCRYPT ('hari prasad'))
declare @.pwd varbinary(128) , @.chk tinyint
-- the dencryption phase
select @.pwd=pswd from users where userid = 1
-- comparing : 1 is success, 0 is not
select @.chk=PWDCOMPARE ('hari prasad',@.pwd)
if @.chk ! = 1
Print 'Wrong Password Entered! Try Again'
else
Print 'Login Successfully'
Do a check inside application , if the value returned is "1" allow to
login.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
> Hello,
> I need to create one table where i want to add records
> with users and corresponding passwords but these passwords
> must appear encrypted.
> How can i do this? and if it is possible how can i decrypt
> these passwords.
> I need to do it but i cant put in risk the security of my
> databases. Is it possible?
> Best regards
|||Thanks Narayana
Best regards

>--Original Message--
>This link will give you an overview of column level
encryption:
>http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
>Hello,
>I need to create one table where i want to add records
>with users and corresponding passwords but these passwords
>must appear encrypted.
>How can i do this? and if it is possible how can i decrypt
>these passwords.
>I need to do it but i cant put in risk the security of my
>databases. Is it possible?
>Best regards
>
>.
>

Create table with Encrypted passwords

Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regardsThis link will give you an overview of column level encryption:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regards|||Hi,
The PWDENCRYPT and PWDCOMPARE functions are used to encrypt and compare DATA
passwords are not visible in anywhere in the database.
FYI, PWDENCRYPT and PWDCOMPARE are undocumented functions , so it can change
in future versions.
Sample code to create table and encypt the password column and usage:-
Create table users ( userid int identity (1,1) not null,
pswd varbinary (128))
-- INSERTING ENCRYPED value
-- hard coded string should be replace
-- by a text box value from screen
Insert into users values (PWDENCRYPT ('hari prasad'))
declare @.pwd varbinary(128) , @.chk tinyint
-- the dencryption phase
select @.pwd=pswd from users where userid = 1
-- comparing : 1 is success, 0 is not
select @.chk=PWDCOMPARE ('hari prasad',@.pwd)
if @.chk ! = 1
Print 'Wrong Password Entered! Try Again'
else
Print 'Login Successfully'
Do a check inside application , if the value returned is "1" allow to
login.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
> Hello,
> I need to create one table where i want to add records
> with users and corresponding passwords but these passwords
> must appear encrypted.
> How can i do this? and if it is possible how can i decrypt
> these passwords.
> I need to do it but i cant put in risk the security of my
> databases. Is it possible?
> Best regards|||Thanks Narayana
Best regards
>--Original Message--
>This link will give you an overview of column level
encryption:
>http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
>Hello,
>I need to create one table where i want to add records
>with users and corresponding passwords but these passwords
>must appear encrypted.
>How can i do this? and if it is possible how can i decrypt
>these passwords.
>I need to do it but i cant put in risk the security of my
>databases. Is it possible?
>Best regards
>
>.
>

Create table with Encrypted passwords

Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regardsThis link will give you an overview of column level encryption:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx
.gbl...
Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regards|||Hi,
The PWDENCRYPT and PWDCOMPARE functions are used to encrypt and compare DATA
passwords are not visible in anywhere in the database.
FYI, PWDENCRYPT and PWDCOMPARE are undocumented functions , so it can change
in future versions.
Sample code to create table and encypt the password column and usage:-
Create table users ( userid int identity (1,1) not null,
pswd varbinary (128))
-- INSERTING ENCRYPED value
-- hard coded string should be replace
-- by a text box value from screen
Insert into users values (PWDENCRYPT ('hari prasad'))
declare @.pwd varbinary(128) , @.chk tinyint
-- the dencryption phase
select @.pwd=pswd from users where userid = 1
-- comparing : 1 is success, 0 is not
select @.chk=PWDCOMPARE ('hari prasad',@.pwd)
if @.chk ! = 1
Print 'Wrong Password Entered! Try Again'
else
Print 'Login Successfully'
Do a check inside application , if the value returned is "1" allow to
login.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx
.gbl...
> Hello,
> I need to create one table where i want to add records
> with users and corresponding passwords but these passwords
> must appear encrypted.
> How can i do this? and if it is possible how can i decrypt
> these passwords.
> I need to do it but i cant put in risk the security of my
> databases. Is it possible?
> Best regards|||Thanks Narayana
Best regards

>--Original Message--
>This link will give you an overview of column level
encryption:
>http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1c14201c45211$ab44f000$a101280a@.phx
.gbl...
>Hello,
>I need to create one table where i want to add records
>with users and corresponding passwords but these passwords
>must appear encrypted.
>How can i do this? and if it is possible how can i decrypt
>these passwords.
>I need to do it but i cant put in risk the security of my
>databases. Is it possible?
>Best regards
>
>.
>

create table with dynamic constraint

I am trying to create a table with the type of constraint I don't see in any
of the help resources.
Say the basic table structure for table t1 is (colType int, colDesc
varchar(10), colMiscellaneous varchar(100))
I want to limit the combination colType-colDesc thusly:
If the combination is new, it's okay.
If the combination is exactly the same as one previously used, it's okay.
If the colDesc is the same as one previously entered, but the colType is
different, the constraint is violated and the insert or update operation
aborts.
Is this even doable? I've used multi-column constraints before, but not in
this way.
Thanks in advance,
DaveIn t-SQL, you cannot have a query expression in a CHECK constraint, so
multi-row checks are not easy to implement declaratively. You can have a
scalar UDF in certain cases, but it might fail for UPDATE operations. So one
option is to use a trigger like:
CREATE TRIGGER trg ON t1 FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT <> 0 RETURN
IF EXISTS ( SELECT * FROM inserted i
WHERE EXISTS ( SELECT * FROM t1
WHERE t1.type = i.type
AND t1.descr <> i.descr )
) ROLLBACK
... -- add any error messages if needed.
Anith|||>> IF @.@.ROWCOUNT <> 0 RETURN
should be = 0 to see if there are any rows affected
Anith|||Dave,
I think an easier solution here would be to maintain two tables:
create table cols (
colType int not null primary key,
colDesc varchar(10)
)
create table colMisc (
colType int not null references cols(colType),
colMiscellaneous varchar(100)
)
This enforces the data integrity you want:
A single colType cannot have more than one description
A colMiscellaneous value must be associated with a colType and colDesc
You could preserve an interface like you have by creating a view to match
your current table, on which there is an INSTEAD OF trigger to perform
the one or two insert statements needed for each addition of a
colMiscellaneous
value. It may not seem like less work to do this, but it avoids what you're
awkwardly doing now, which is storing facts like "the description of
column #N
is blahblah" once for every colMiscellaneous value there happens to be
for that
column.
In the long run, what you're doing will likely get you into trouble that
you have to solve with more awkwardness, like by adding DISTINCT
to queries that shouldn't need it.
Steve Kass
Drew University
Dave wrote:

>I am trying to create a table with the type of constraint I don't see in an
y
>of the help resources.
>Say the basic table structure for table t1 is (colType int, colDesc
>varchar(10), colMiscellaneous varchar(100))
>I want to limit the combination colType-colDesc thusly:
>If the combination is new, it's okay.
>If the combination is exactly the same as one previously used, it's okay.
>If the colDesc is the same as one previously entered, but the colType is
>different, the constraint is violated and the insert or update operation
>aborts.
>Is this even doable? I've used multi-column constraints before, but not in
>this way.
>Thanks in advance,
>Dave
>
>|||That is an excellent point, and one that I had considered. However, there
really are only three columns, this is just an ancillary table of about 50
rows that will not get many hits, and there will be only one routine for
each of the operations (SELECT, INSERT, UPDATE, & DELETE). I was also just
curious how I would accomplish such a task.
I do know enough about normalization to recognize your solution is
theoretically better; in this case I think the fewer tables factor will
outweigh the drawbacks you point out.
Thanks,
Dave
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23S36LBWbFHA.3384@.TK2MSFTNGP09.phx.gbl...
> Dave,
> I think an easier solution here would be to maintain two tables:
> create table cols (
> colType int not null primary key,
> colDesc varchar(10)
> )
> create table colMisc (
> colType int not null references cols(colType),
> colMiscellaneous varchar(100)
> )
> This enforces the data integrity you want:
> A single colType cannot have more than one description
> A colMiscellaneous value must be associated with a colType and colDesc
> You could preserve an interface like you have by creating a view to match
> your current table, on which there is an INSTEAD OF trigger to perform
> the one or two insert statements needed for each addition of a
> colMiscellaneous
> value. It may not seem like less work to do this, but it avoids what
you're
> awkwardly doing now, which is storing facts like "the description of
> column #N
> is blahblah" once for every colMiscellaneous value there happens to be
> for that
> column.
> In the long run, what you're doing will likely get you into trouble that
> you have to solve with more awkwardness, like by adding DISTINCT
> to queries that shouldn't need it.
> Steve Kass
> Drew University
> Dave wrote:
>
any

Create Table with current date as part of the table name

Afternoon all,

Is it possible from within SQL Server Management Studio to create a table based upon an existing table using the current date as part of the table name?

I.E; SELECT * FROM TABLENAME INTO TABLENAMEWITHDATE - if this query was setup as a SSMS Agent Job we could create a daily snapshot of data in this table.

I've tried many times but always get an incorrect syntax message when I try to excecute the query. I'm not sure what syntax I should use to create the tablename with current date included?

Any help would be appreciated.

Thanks,

Chris

Though I am wary of what you are trying to do (a permanent table with a column fro the load date is usually easier to work with,) you could use dynamic SQL:

declare @.tableName varchar(8), @.query nvarchar(1000)

set @.tableName = convert(varchar(8), getdate(),112)

select @.query = 'select name into ' + quotename(@.tableName) + ' from sys.objects'

exec (@.query)

select *
from sys.objects
where name = @.tableName

|||

Thanks, Louis, you've been a great help.

If you ever find yourself lost in Chepstow I'll definately be buying your drinks.

Chris

sql

Create Table with current date as part of the table name

Afternoon all,

Is it possible from within SQL Server Management Studio to create a table based upon an existing table using the current date as part of the table name?

I.E; SELECT * FROM TABLENAME INTO TABLENAMEWITHDATE - if this query was setup as a SSMS Agent Job we could create a daily snapshot of data in this table.

I've tried many times but always get an incorrect syntax message when I try to excecute the query. I'm not sure what syntax I should use to create the tablename with current date included?

Any help would be appreciated.

Thanks,

Chris

Though I am wary of what you are trying to do (a permanent table with a column fro the load date is usually easier to work with,) you could use dynamic SQL:

declare @.tableName varchar(8), @.query nvarchar(1000)

set @.tableName = convert(varchar(8), getdate(),112)

select @.query = 'select name into ' + quotename(@.tableName) + ' from sys.objects'

exec (@.query)

select *
from sys.objects
where name = @.tableName

|||

Thanks, Louis, you've been a great help.

If you ever find yourself lost in Chepstow I'll definately be buying your drinks.

Chris

CREATE TABLE with a DEFAULT for Microsoft Access

I'm missing SQL Server already. :(

This doesn't work with a Microsoft Access database. The DEFAULT is causing a syntax exception. Trying to find any help with Google has prooved very frustrating and given me no leads, so do any of you know how it is done in a CREATE TABLE statement? (i.e. not seperately).

CREATE TABLE [MyTable] (
[MyField] VARCHAR(50) DEFAULT ""
)

Thanks for reading,

- David

(btw, I posted this in the general SQL forum as there didn't seem to be one for non-application-type MS Access questions. Hope that was right.)When you use the Table Design within Access there is Default Value property for a column.
By the way there is a Microsoft Access section with dbforums.|||I know about that, I want to set it using an SQL statement though. I am creating the database tables through script not using Access itself.

I mentioned why I didn't use the Microsoft Access forum in my edit. I looked at the messages that were on the first few pages and they seemed to all be application-orientated.

Thanks for your reply,

- David|||Ok, but it is just a suggestion to maybe have your question duplicated in the MS Access (you never know who might be popping in there to view stuff).

Also, have you looked at the Access documentation there is a section about Jet SQL Reference (not sure if that is what you need to reference)...although it looks as though there isn't a mention of DEFAULT. I agree with you when you 'downgrade' from a DB engine that has everything to something that lacks, it is frustrating.

Good luck....|||CREATE TABLE [MyTable] (
MyField Text(50) DEFAULT Hello World,
MyID Integer NOT NULL DEFAULT 1
)

Sorry for pulling a Hello world stuff on ya but that should work.|||I tried running that SQL in MS Access itself and got the same error I have been seeing with other attempts:

---------------
Microsoft Access
---------------
Syntax error in CREATE TABLE statement.
---------------
OK Help
---------------

It then selects the CREATE keyword in the SQL window.

I am using Access 2002/XP for this, should I be using something else?|||Just an observation, but MS-Access is a client side program. By default, it ships with the Microsoft-Jet database engine. If you have MS-Access 2002, you have MSDE on the CD, which is a slightly scaled down version of MS-SQL.

It might be worthwhile for you to install MSDE and use that as your database engine. It would put you on much more familiar ground!

-PatP|||Thanks Pat, but it's not for that sort of use. The product gets installed on web servers that don't have SQL Server or MSDE available to them. (if it does, it would use them anyway). :(

Create table with 15,000,000 default rows

Hi I want to create a table with one column, which is a identity
column.
Let's say like this:
CREATE TABLE DefaultTable(N int identity(0,1))

Then I want to fill this table with 15,000,000 records, so that I have
a table with the numbers 0 to 14,999,999.

How can I do this as fast as possible. A standard INSERT would take a
long time.

(It can be a temp table or a table variable. I just need a list with
numbered 0 to 15,000,000)

Thank you.

Gidonhttp://www.bizdatasolutions.com/tsql/tblnumbers.asp
--
David Portas
SQL Server MVP
--|||Thanks a lot.

Create table using data from another table

Hi,
How could I create a new table dynamically where columns names are data
from another table?
Example:
I have a table "Table1" with one column "T1Col"
The column contains following data:
"Row1"
"Row2"
"Row3"
Now I would like to "read" data from Table1 and create a new table
Table2 which will contain columns "Row1", "Row2" and
"Row3".
Any help will be appreciated.
Thank you in advance.Google for "transpose" or "cross-tab" or search this newsgroup.
ML
http://milambda.blogspot.com/sql

create table test

hi,
What is the syntax for creating a new table as that of
existing one with data..
create table test1 as select * from test is not working.
Regards
KrishSELECT * INTO NewTable FROM OldTable
--
Rohtash Kapoor
http://www.sqlmantra.com
<anonymous@.discussions.microsoft.com> wrote in message
news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> hi,
> What is the syntax for creating a new table as that of
> existing one with data..
> create table test1 as select * from test is not working.
>
> Regards
> Krish|||Hi,
To add on, this command just copies the table structure and data. Indexes ,
Constraints and Identity property
needs to be created manually.
--
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> SELECT * INTO NewTable FROM OldTable
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > hi,
> >
> > What is the syntax for creating a new table as that of
> > existing one with data..
> >
> > create table test1 as select * from test is not working.
> >
> >
> > Regards
> > Krish
>|||That's right. However, IDENTITY property will be copied to new table.
--
Rohtash Kapoor
http://www.sqlmantra.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Hi,
> To add on, this command just copies the table structure and data. Indexes
,
> Constraints and Identity property
> needs to be created manually.
> --
> Thanks
> Hari
> MCDBA
> "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > SELECT * INTO NewTable FROM OldTable
> >
> > --
> > Rohtash Kapoor
> > http://www.sqlmantra.com
> >
> >
> >
> > <anonymous@.discussions.microsoft.com> wrote in message
> > news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > > hi,
> > >
> > > What is the syntax for creating a new table as that of
> > > existing one with data..
> > >
> > > create table test1 as select * from test is not working.
> > >
> > >
> > > Regards
> > > Krish
> >
> >
>|||Hi,
Yes, That is correct.
--
Thanks
Hari
MCDBA
"Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
news:#diRfGMZEHA.556@.tk2msftngp13.phx.gbl...
> That's right. However, IDENTITY property will be copied to new table.
> --
> Rohtash Kapoor
> http://www.sqlmantra.com
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OhLA1xLZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > To add on, this command just copies the table structure and data.
Indexes
> ,
> > Constraints and Identity property
> > needs to be created manually.
> >
> > --
> > Thanks
> > Hari
> > MCDBA
> > "Rohtash Kapoor" <rohtash_nospam@.sqlmantra.com> wrote in message
> > news:#utu#kLZEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > > SELECT * INTO NewTable FROM OldTable
> > >
> > > --
> > > Rohtash Kapoor
> > > http://www.sqlmantra.com
> > >
> > >
> > >
> > > <anonymous@.discussions.microsoft.com> wrote in message
> > > news:2834b01c464b6$92062c00$a601280a@.phx.gbl...
> > > > hi,
> > > >
> > > > What is the syntax for creating a new table as that of
> > > > existing one with data..
> > > >
> > > > create table test1 as select * from test is not working.
> > > >
> > > >
> > > > Regards
> > > > Krish
> > >
> > >
> >
> >
>

Thursday, March 29, 2012

Create tables in SQL2K from XSD schema?

Create tables from master table

Create Tables Dynamically

create tables and insert data in sql server mobile on dekstop

Create table?

CREATE TABLE/VIEW from stored procedure or SELECT...

Create table,fields come from csv file

Create table, table name as procedure parameter ?

Create table, remove records?

Create table, remove records?

Create table, concatenate data

Create table, concatenate data

Create table, concatenate data

create table(s) from xml schema

create table(s) from xml schema

CREATE TABLE without columns

Create Table within an IF statement causes error

Create Table with variable name

Create Table with Unknown Table Name?

create table with TRIGGER

create table with recursive relationship

Create table with PK on two columns

CREATE TABLE with multiple-column primary key?

Create Table with Foreign Keys

Create table with Encrypted passwords

Create table with Encrypted passwords

Create table with Encrypted passwords

create table with dynamic constraint

Create Table with current date as part of the table name

Create Table with current date as part of the table name

CREATE TABLE with a DEFAULT for Microsoft Access

Create table with 15,000,000 default rows

Create table using data from another table

create table test

create table test

Sql Create error

Blog Archive

About Me