script

Question

script

harri.reddy

SSCommitted

Points: 1957
More actions
November 9, 2012 at 8:02 am

#393574

hi,
need script that transfer data from 1 table into another.
Old table :
CREATE TABLE [dbo]Master](
[Format] [varchar](11) NOT NULL,
[Class] [varchar](50) NULL,
[Product_Name] [varchar](100) NULL,
[Form_Code] [varchar](150) NULL,
[Strength] [varchar](50) NULL)
sample data :
Format Class Product_name Form_code Strength
0002-345 2 abc tab 300 MG
0056-345 null xyz sol 300 MG
4567-32 3 fgh tgb 56 MG
new table :
CREATE TABLE [dbo]child](
[Format] [varchar](11) NOT NULL,
[Class] [varchar](50) NULL,
[Product_Name] [varchar](100) NULL,
[Form_Code] [varchar](150) NULL,
[Strength] [varchar](50) NULL,
unit varchar(50))
sample data after inserting from old table :
Format Class Product_name Form_code Strength unit
0002-345 2 abc tab 300 MG
0056-345 null xyz sol 300 MG
now before transfering i need to see ,if format and classname are match with data in new table then just ignore,if not then insert row.
also in my old table i have strengh ,when i am inserting into new table , it shoudl be seperated in two field strenght and unit.
it is possible to write a script , that i can use in future also.for example, every time i need to change table name from script,when i am inserting from
old to new table.
any dynamic way

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

I suggest that you look at the MERGE statement (new in sql 2008) that said here is a good discussion with excellent examples that might be of use to you.

http://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

MERGE is overkill if all he wants to do is a conditional insert.

it is possible to write a script , that i can use in future also.for example, every time i need to change table name from script,when i am inserting from old to new table

Are you talking about reusing this script for two tables using the same schema and the same transfer logic?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

harri.reddy SSCommitted Points: 1957 More actions · Answer 3

well isee that example.

but in my case, if they match i ,need to do nothing.

hwo to specify that in merge.

harri.reddy SSCommitted Points: 1957 More actions · Answer 4

no different login and different schema.

also how to seperate 23mg into 23 and mg ,like letter should be seperated from digits

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

harri.reddy (11/9/2012)
no different login and different schema.
also how to seperate 23mg into 23 and mg ,like letter should be seperated from digits

I'm pretty sure that you've been told previously how to get the best answers for things like this because I'm pretty sure I'm one of the folks that told you ;-). You got half of it by posting the table definitions. Now you need to post some readily consumable data that someone can write a coded example for you from.

Please read and heed the article at the first link in my signature lines below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)