Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Couple of Simple ones..

Author  Topic 

khalik
Constraint Violating Yak Guru

443 Posts
Posted - 2009-01-21 : 09:37:00
Hi

I wish to create a procedure which takes two parameters
a. @xmldata xml type
b. @tablename varchar

@tablename will hold the physical table name
1. i need to create a #table or @table, structure will be same as @tablename.
2. load the data into @/# table from @xmldata.

second look ok but how do i do the first one.


========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-21 : 09:39:12
why pass tablename as a parameter? why does table change dynamically? i dont think this is a good approach
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2009-01-21 : 09:39:20
What are the contents of @tablename variable? is it just table name or table schema? Why you want to pass table structure from outside the SP?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

khalik
Constraint Violating Yak Guru

443 Posts
Posted - 2009-01-21 : 09:52:14
@tablename will be phyisical table name.

A single sp to take xml data and load in to table with some basic operations.
if this is not a better approach then what do u suggest.



========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone
Go to Top of Page

khalik
Constraint Violating Yak Guru

443 Posts
Posted - 2009-01-21 : 10:10:22
Can i some thign like this

Declare @sql varchar(max)
Declare @tablename varchar(100)
set @tablename='Accounts'

set @sql='select * into #t from ' + @tablename

exec(sql)

#t should be created with all rows

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-01-21 : 10:22:39
once your exec statement completes your #t table will be out of scope from the rest of the code. There is no real good, clean way to accomplish #1.

A single, generalized sp to perform similar operations dynamically for any existing physical table is usually not a good approach. To suggest a different approach we would need to know what the big picture objective is.

Be One with the Optimizer
TG
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2009-01-21 : 10:24:11
quote:
Originally posted by khalik

Can i some thign like this

Declare @sql varchar(max)
Declare @tablename varchar(100)
set @tablename='Accounts'

set @sql='select * into #t from ' + @tablename

exec(sql)

#t should be created with all rows

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone


Yes. It will load temporary table with all data you want, but that temp table won't be accessible outside dynamic sql and hence, won't be of any use.

If the schema of the table is constant, then why don't you create a permanent Load table for loading of the data?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

khalik
Constraint Violating Yak Guru

443 Posts
Posted - 2009-01-21 : 10:34:39
i have a .net app which has data in a dataset.

i need to do come cleaning of data before update or insert. my approach was to load this data in temp table clean it and then do batch operations.

if i have to create physical tables. i will be duplicating which i dont want to.

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-01-21 : 10:36:26
How many different physical tables will you need to be doing this for?

Be One with the Optimizer
TG
Go to Top of Page

khalik
Constraint Violating Yak Guru

443 Posts
Posted - 2009-01-21 : 10:42:09
any thign from 1 to 100 tables.

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-21 : 11:04:08
quote:
Originally posted by khalik

any thign from 1 to 100 tables.

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone

then cant you use ssis for this cleansing operation? just use foreachloop with data flow task inside it
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-01-21 : 11:05:50
sorry to keep asking questions without suggesting anything but...

What is the nature of "cleaning of data" that you need to do. It must be very generalized if the same code will work on 100 different tables?

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2009-01-21 : 11:06:38
yes Visakh, that is what I was thinking as well...

Be One with the Optimizer
TG
Go to Top of Page

khalik
Constraint Violating Yak Guru

443 Posts
Posted - 2009-01-22 : 03:00:10
Thanks guys, Actually this tool be part of smart client which run on sql express. it get the data and then updates the local db. when i mean cleaning i mean check of integrity of data and insert / update operation.

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-22 : 09:01:35
that can be done by means of ssis or even by means of simple procedure. what are integrity checks you would be interested in?
Go to Top of Page

khalik
Constraint Violating Yak Guru

443 Posts
Posted - 2009-01-23 : 04:57:23
Hi Visakh16

Let me explain the whole problem.
i have a smart client application which need data sync. Based on several business rule & user roles i get the dataset which need to updated at client db. the whole i can put in SP is required.

I have a db server which is not directly accessible to client.
so my approach was to put up a webservices which get the data writes a xml and then transfer the xml to client side. client process table by table to DB that where i planned to use XML and XQuery to process.

Let me know if any other info is required to suggest a better approach.


========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-23 : 09:25:46
ok. in that case its simply a matter of procedure which parses xml and extracts data to table.use a xml datatype parameter in sp.
Go to Top of Page

khalik
Constraint Violating Yak Guru

443 Posts
Posted - 2009-01-23 : 09:29:17
Hi Visakh16 i hope life would have been so simple.. i tried a sample and have some issue with it.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118469

when execute select using for xml path then the output i copy to xml file save it. try to take that as xm source in SSIS then no columns are displayed why is that so?

========================================
Project Manager who loves to code.
===============
Ask to your self before u ask someone
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site