Inserts multiple times with a specific format

Question

Post reply

Inserts multiple times with a specific format

The_SQL_DBA

SSChampion

Points: 10494
More actions
December 11, 2009 at 9:33 am

#218640

Hi All, I need help inserting data from source table into a destination table with a specific format..example below shows one row in the new format, i need to do this for the whole table..
SOURCE
membershipidmembernamemembershiptype
424400000000Andre SimPremium
DESTINATION
membershipid membername membershiptype Flag
4244 Andre Sim Premium 1
42440000 Andre Sim Premium 1
424400000000 Andre Sim Premium 1
How do I select part of the membership id and insert into destination where the first insert will have only first 4 chars from membershipid and all other columns copied the same, the second time select 8 chars from membeshipid and all the other columns the same and the last insert would be all 12 chars from membershipid and the other columns copied same..
PS: the flag field is 1 if the last eight chars are 0's, 2 if the last 4 are 0's else its 3 (at least one char in last 4 is not 0)
Basically, its copying each source row into destination 3 times with only the membershipid being broken into parts with every insert..is there a good feature in SQL 2008 to support this?
Any help would be appreciated..
Thanks in advance!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Viewing 8 posts - 1 through 7 (of 7 total)

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

RyanRandall SSChampion Points: 13623 More actions · Answer 1

This?

declare @SOURCE table (membershipid varchar(100), membername varchar(100), membershiptype varchar(100))

insert @SOURCE

select 424400000000, 'Andre Sim', 'Premium'

union all select 424400010000, 'Hello', 'World'

union all select 424400010001, 'Hello2', 'World2'

; with t1 as (

select *,

case when right(membershipid, 8) = '00000000' then 1

when right(membershipid, 4) = '0000' then 2

else 3 end as Flag

from @SOURCE)

select * from (

select substring(membershipid, 1, 4) as membershipid, membername, membershiptype, Flag from t1

union all select substring(membershipid, 1, 8), membername, membershiptype, Flag from t1

union all select membershipid, membername, membershiptype, Flag from t1) a

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

sturner SSC-Insane Points: 22411 More actions · Answer 2

try this:

insert destination (membershipid membername membershiptype Flag)

select substring(membershipid,1,4), membername, membershiptype, 1

from source

union all

select substring(membershipid,4,4), membername, membershiptype, 1

from source

union all

select substring(membershipid,8,4), membername, membershiptype, 1

from source

The probability of survival is inversely proportional to the angle of arrival.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 3

Thanks Ryan and Turner..I will try out both and let you guys know which one works for me.

As I have in excess of 20K records that i need to split in this format and insert into destination table from the source table..

Thanks again for the quick response..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Pavel Pawlowski Ten Centuries Points: 1244 More actions · Answer 4

Or you can try using a CROSS JOIN to multiply the inserts and threrefore you will have only one table scan instead of 3 table scans when using the union.

DECLARE @source TABLE (

membershipID varchar(12),

membershipName varchar(20),

membershipType varchar(20)

)

INSERT INTO @source (

membershipID,

membershipName,

membershipType

)

SELECT '424400000000' AS ID , 'Andre Sim' AS Name, 'Premium' AS Type

;WITH Repeats AS (

SELECT 4 AS Cnt UNION ALL

SELECT 8 AS Cnt UNION ALL

SELECT 12 AS Cnt

)

SELECT

LEFT(S.membershipID, R.Cnt),

S.membershipName,

S.membershipType,

CASE WHEN RIGHT(S.membershipID, 8) = '00000000' THEN 1

WHEN RIGHT(s.membershipID, 4) = '0000' THEN 2

ELSE 3

END As Flag

FROM @source S

CROSS JOIN Repeats R

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 5

Ryan, I tried your query, it works good, but the format I need is different. I am posting your result set as attached along with the desired format from your source data..let me know if I can be more clear on this..

Thanks..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

sturner SSC-Insane Points: 22411 More actions · Answer 6

just add an order by to the union queries if you need a specific insert order.

The probability of survival is inversely proportional to the angle of arrival.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 7

Pavel..your query returns result in the format I needed. I need to use it to do the inserts now..will follow up with you guys if I get stuck somewhere 😉

Thanks..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."