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) = ''

AS

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

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.
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...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

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 b
e
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 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?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 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.

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

go

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

go

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

AS

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'
>
>