August 9, 2011 at 8:28 am
I'm sorry - that was in response to an earlier post -- the structures are:
CREATE TABLE [dbo].[RND_Street](
[ID] [int] IDENTITY(1,1) NOT NULL,
[street_name] [varchar](40) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[RND_Customer_Master](
[Customer_Id] [int] IDENTITY(695,1) NOT NULL,
[Customer_Type] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Address1] [varchar](50) NULL,
[Address2] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [varchar](50) NULL,
[Zip] [varchar](50) NULL,
[Phone] [varchar](50) NULL,
[Phone_Ext] [varchar](10) NULL,
[Phone_Ext2] [varchar](10) NULL,
[Fax_Number] [varchar](50) NULL,
[Contact] [varchar](50) NULL,
[Contact2] [varchar](50) NULL,
) ON [PRIMARY]
August 9, 2011 at 8:32 am
And some sample data for the two please (garbage data is fine, not asking for anything confidential)/ Also a bit better description of what you want to happen.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2011 at 8:35 am
This can script that data for you in 2 secs. Just found about it today and it's awesome!
August 9, 2011 at 8:42 am
GilaMonster (8/9/2011)
And some sample data for the two please (garbage data is fine, not asking for anything confidential)/ Also a bit better description of what you want to happen.
rnd_street:
IDstreet_name
93 Lincoln Way
94 E Broad St.
95 10th Ave Room 230
96 NEW BRUNSWICK AVE
97 Lake Ave
98 S. Delsea Drive
99 Apple Tree Lane
100 N. State St.
101 KRUSE DR.
102 5th Ave
103 William Penn PL Rm 2765
104 E Roosevelt Blvd
105 Military Highway
106 N 9th St
107 Route 38 W
108 N Park Ave Suite 101
109 South Division, Suite C
110 FLANDERS RD
111 S MIAMI BLVD
112 W. Ehringhaus St.
113 E 7TH STREET
114 Tuckerton Rd
115 N. TRYON ST.
116 Auto Park Circle
117 Route 17 South
118 Route 23
119 Ft. Meade Rd.
120 Broad Avenue
121 S. Minnesota Ave.
122 Britton Parkway
123 Lee St NE
124 HACKENSACK AVE
125 POST RD EAST
126 HWY 70 SE
127 Linden Street
128 East Belt Boulevard
129 NE 11th Avenue
130 N. 7th Street
131 COAL STREET
132 Professional Place Suite 308
133 Santa Margurita Parkway
134 Valley Rd.
135 NW 14 STREET
136 John Hopkins RD
137 HIGHWAY36
138 COLEMAN ST
139 Myrtle Avenue
140 Horsham Road
141 S. Pacific St.
142 Fountain Plaza P.O. Box 4005
143 West Main St.
144 Truman Blvd
145 N Wisconsin St
146 E. Rosa Park Ave.
147 W. Court, Room 102
148 Caldwell Dr
149 St Route 73S Bldg 12
150 S Church St
151 Superior Way
152 N. Kenmore Street
153 Starksville Ave
154 Macdade Blvd
155 SHELBYVILLE ROAD
156 East 161st Street
157 Morris Ave.
158 Industrial Ave
159 Courthouse Square Suite 101
160 west 43rd St.
August 9, 2011 at 8:44 am
No offence, but that'll be a pain in the neck to get into a table. Read this to see the best way to post sample data.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Also want some rows for the other table please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 9, 2011 at 8:46 am
>>>>>
Ninja's_RGR'us (8/9/2011)
This can script that data for you in 2 secs. Just found about it today and it's awesome!
Or use Gail's fine article too. The tool just automates it for ya.
August 9, 2011 at 12:32 pm
Not the most effecient, but I think might work for what you are trying to do. If not, check out what Gail posted, so we can help you better:update
RND_Customer_Master
set
Address1 =
(
select TOP 1 convert(varchar(5),dbo.RandNumberInt(1, 100000)) + ' ' + street_name
from rnd_street
--where ID = dbo.RandNumberInt(1, 1000)
ORDER BY NEWID()
),
RoutingNumber = convert(varchar(10), ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 1000000),
BankAcctNumber = convert(varchar(10), ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 1000000),
Zip = convert(varchar(5), ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 100000),
first_name =
(
select TOP 1 contact
from RND_contact
--where ID = dbo.RandNumberInt(1, 1000)
ORDER BY NEWID()
),
name =
(
select TOP 1 first_name + last_name
from RND_LNAME, RND_FNAME
where
--RND_FNAME.ID = dbo.RandNumberInt(1, 1000)
--and
RND_LNAME.ID = RND_FNAME.ID
ORDER BY NEWID()
)
EDIT - had a couple typos and for got a TOP clause..
August 9, 2011 at 12:36 pm
Lamprey13 (8/9/2011)
Not the most effecient, but I think might work for what you are trying to do. If not, check out what Gail posted, so we can help you better:
Thank you -- that looks like it'll work.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply