data table foriegn key problem

I am just learning how to program in SQL, so please be patient with me

ok, here it is, i am getting the following errors with my script when i try to execute, and although i realize that this is not the best style to write my script in, i am trying to learn why this is erroring. the errors are:

Msg 1769, Level 16, State 1, Line 9

Foreign key 'Employee2Job_Title' references invalid column 'Title' in referencing table 'Employee'.

Msg 1750, Level 16, State 0, Line 9

Could not create constraint. See previous errors.

now i realize that the second error is because of the first, and that if i can fix the first, the second will go away. Thanks for the help.

Here is my script

use inventory

go

CREATE TABLE Job_Title

(Job_Title_Title char(25) NOT NULL ,

Job_Title_EEO1_Classification char(25) ,

Job_Title_Job_Description Varchar(45) ,

Job_Title_Exempt_Status Varchar(15)

, PRIMARY KEY (Job_Title_Title)

);

CREATE TABLE Employee

(Employee_Emp_ID integer NOT NULL ,

Employee_last_name varchar(15) ,

Employee_first_name varchar(15) ,

Employee_address varchar(30) ,

Employee_city varchar(15) ,

Employee_state char(2) ,

Employee_Telephone_area_code char(3) ,

Employee_Telephone_number char(8) ,

Employee_EEO1_Classification char(25) ,

Employee_Hire_Date char(8) ,

Employee_Salary char(6) ,

Employee_Gender Varchar(1) ,

Employee_Age char(2) ,

Employee_Title char(25)

, PRIMARY KEY (Employee_Emp_ID)

, constraint Employee2Job_Title FOREIGN KEY (Title

) REFERENCES Job_Title

);

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(1, 'Edelman', 'Glenn', '175 Bishop Lane', 'La Jolla', 'CA', 619, '555-0199', 'Sales Workers', 10/7/2003, 21500.00, 'M', 64, 'Cashier')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(2, 'McMullen', 'Eric', '762 Church Street', 'Lemon Grove', 'CA', 619, '555-0133', 'Sales Workers', 11/1/2002, 13500.00, 'M', 20, 'Bagger')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(3, 'Slenj', 'Raj', '123 Torrey Drive', 'North Clairmont', 'CA', 619, '555-0123', 'Officials & Managers', 6/1/2000, 48000.00, 'M', 34, 'Assistant Manager')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(4, 'Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA', 760, '555-0100', 'Sales Workers', 3/12/2003, 10530.00, 'F', 24, 'Bagger - 30 hours/wk')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(5, 'Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', 619, '555-0154', 'Office/Clerical', 11/1/2003, 15000.00, 'M', 18, 'Stocker')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(6, 'Esquivez', 'David', '10983 North Coast Highway Apt 902', 'Encinitas', 'CA', 760, '555-0108', 'Operatives (Semi skilled)', 7/25/2003, 18500.00, 'M',25, 'Asst. - Butchers & Seafood Specialists')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(7, 'Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', 858, '555-0135', 'Sales Workers', 7/12/2003, 21000.00, 'F', 24, 'Cashier')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Accounting Clerk', 'Office/Clerical', 'Computes, Classifies, records, and verifies numerical data for use in maintaining accounting records.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Assistant store manager', 'Officials & Mangers', 'Supervises and coordintes activities of workers in department of food store. Assist store manager in daily operations of store.', 'Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Bagger', 'Sales Worker', 'Places customer orders in bags. Performs carry out duties for customers.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Cashier', 'Sales Worker', 'Operates Cash register to itemize and total customers purchases in grocercy store', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Computer Support Specialist', 'Technician', 'Installs, Modifies, and makes minor repairs to personal computer hardware and software systems and provides technical assistance and training to system users.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Director of Finance & Accounting', 'Officials & Mangers', 'Plans and directs finance and accounting activites for Kudlser Fine Foods.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Retail Asst. Bakery & Pastry', 'Craft Workers (Skilled)', 'Obtains or prepares Bakery and Pastry items requested by customers in retail food store.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Retail Asst. Butchers and Seafood Specialists', 'Operatives (Semi skilled)', 'Obtains or prepares Meat and Seafood items requested by customers in retail food store.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Stocker', 'Office/Clerical', 'Stores, prices, and restocks merchandise displays in store.', 'Non-Exempt')

In your foreign key that you are creating with:

constraint Employee2Job_Title FOREIGN KEY (Title) REFERENCES Job_Title

The column named Title needs to exist in the Employee table on which you are creating the constraint. Then for the column it points to with the REFERENCES part, the needs to be in the format of ReferenceTable(ReferenceTableColumnName)

If I'm guessing right at your tables, I think what you are looking for is more along the lines of

constraint Employee2Job_Title FOREIGN KEY (Employee_Title) REFERENCES Job_Title(Job_Title_Title)

-Sue

|||ok, here is the code i ended up with that cured that problem

CREATE TABLE Job_Title
(Job_Title_Title char(25) NOT NULL ,
Job_Title_EEO1_Classification char(25) ,
Job_Title_Job_Description Varchar(45) ,
Job_Title_Exempt_Status Varchar(15)
, PRIMARY KEY (Job_Title_Title)
);

CREATE TABLE Employee
(Employee_Emp_ID integer NOT NULL ,
Employee_last_name varchar(15) ,
Employee_first_name varchar(15) ,
Employee_address varchar(30) ,
Employee_city varchar(15) ,
Employee_state char(2) ,
Employee_Telephone_area_code char(3) ,
Employee_Telephone_number char(8) ,
Employee_EEO1_Classification char(25) ,
Employee_Hire_Date char(8) ,
Employee_Salary char(6) ,
Employee_Gender Varchar(1) ,
Employee_Age char(2) ,
Employee_Title char(25)
, PRIMARY KEY (Employee_Emp_ID)
, constraint Employee2Job_Title FOREIGN KEY (Employee_Title
) REFERENCES Job_Title
);

but now i am on to new and even more frustrating errors, and i will do some work on them myself, on the morrow, then if i am still having problems, i will again avail myself of this resource.

thanks much sue, your assistance has been invaluable!sql

Data scripting tool ( NOT DATABASE scripting)

Hi to all
Is there any option in sql server DTS or any other third party tool that can script data. By scripting data i mean that...

if a table "Employee" contains 50 rows, i want the tool to write 50 insert queries for me so that i can run in it anywhere.

Problem is i have to insert data in a remote server where i cannot use DTS. I just have a text area to write my query and press the run button..

Hope u understand my problem. In case of any explanation please reply. Waiting for your response. Thanx in advance.

by to allPretty expensive but they have 14 day trial. I am looking for a shareware tool too.

http://www.apexsql.com/frame_home.htm

Data script task - how to generate multiple rows from one row ? (string splitter)

Hi

in input we have a set of rows, each one with a column containing a string (eg: "AAOOOOAAAOOA").

We'd like to split this string (using a vb.net data script task) into tokens (eg: "AA", then "OOOO","AAA","OO","A"), and to output one line per token.

How can we achieve that with a vb.net data script task ? (Or anything else ?)

best regards

ThibautJust take your source and throw it into a derived column transformation where you'll perform your string split to create new fields for each "token." Then you'll go into an unpivot transformation where you'll take the new columns and turn them into rows.

Why use a script task when you can use the optimized data flow tools as-is?|||

You need to create an asynchrnous transform. So add the transform, select and also setup columns as required. Ensure the SynchronousInputID of the output is set to 0, making it async.

Then read rows and add them to the output as required. Sum dummy code -

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

' Read the rows in...

While Row.NextRow() ' This happens once for each input row

' Do something here, and as required, add rows to the output, use a loop or whatever

For i As Int = i < 10

With OutputBuffer

.AddRow() ' Adding a new output row and setting values. Can do this as many times as we like, 0 or more times in the context of this input row loop interation

.Asset = Row.InputColumn

.Product = i

Next

End While

If Row.EndOfRowset Then

OutputBuffer.SetEndOfRowset()

End If

End Sub

|||Thanks a lot Darren ! That's really perfect (and thanks for the detailed sample, I really appreciate).

regards,

Thibaut|||Just be careful because some have tested the script task and it performs slower than the other, native dataflow tasks.|||Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?|||

DarrenSQLIS wrote:

Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?

Yep, no problem... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=857796&SiteID=1|||Hi Phil

thanks for your input as well. Like Darren, I agree that a good rule of thumb is that a pipeline component will work generally faster than a corresponding script component.

A script component is generally one-shot code, which generally doesn't get the same level of testing and optimization. In the article you point to, the script task benchmark shows an average CPU usage of 5% (which is often a sign of synchronization issues, contention etc). Here (like suggested by a commenter), a modification of the implementation (like handling sets of rows instead of one row at a time) would most likely boost the performance a lot.

So I wouldn't draw conclusions based on a single script example, given that each implementation is likely to vary a lot in terms of performance and memory consumption - just like any kind of code!

But anyway - it seems that I have two solutions for my problem now. I don't hesitate to use script task when they prove useful, but I first try to stick to the pipeline components.

I'll keep you posted!

thanks again for the input

Thibaut Barrère|||Hi

I've finished the job using a script task (I will report back the details later).

I'm curious about how it would have been possible to implement this using a derived column transformation (at first sight I couldn't find out).

Phil could you give a bit more details ? I could not find any real string splitting functions in SSIS - were you thinking of using a FIND function recursively (or any other feature I've missed ?)

cheers

Thibaut

Data script

I am writing a script to retrieve data records of MarinLif table. The
problem is that i have a column MarinLif_Picture of type image so the
insert is wrong in this way. How do i correct it?

/*****MarinLIf TAble ********************/
DECLARE @.ID INT
DECLARE @.typeID INT
DECLARE @.NAME NVARCHAR(50)
Declare @.scName nvarchar(50)
Declare @.distribution nvarchar(600)
Declare @.maxSize nvarchar(200)
Declare @.env nvarchar(200)
Declare @.climate nvarchar(200)
Declare @.country nvarchar(2000)
Declare @.desc nvarchar(4000)
Declare @.pic image

DECLARE CURS CURSOR STATIC FOR
SELECT MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
MarinLIf_ScName,MarinLIf_Distribution,
MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
_Desc,MarinLIf_Pic
FROM MarinLif
OPEN CURS
FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
@.maxSize, @.env, @.climate,
@.country, @.desc, @.pic
PRINT 'SET IDENTITY_INSERT MarinLif ON'
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'INSERT INTO MarinLif (MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
MarinLIf_ScName,MarinLIf_Distribution,
MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
_Desc,MarinLIf_Pic)
VALUES (' + convert(varchar,@.ID) + ','
+ convert(varchar,@.typeID) + ','
+ '''' + @.NAME + '''' +
+ '''' + @.scName + '''' + ','
+ '''' + @.distribution + '''' + ','
+ '''' + @.maxSize + '''' + ','
+ '''' + @.env + '''' + ','
+ '''' + @.climate + '''' + ','
+ '''' + @.country + '''' + ','
+ '''' + @.desc + '''' + ','
+ '''' + @.climate + '''' + ','
+ '''' + @.pic + '''' + ')'
FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
@.maxSize, @.env, @.climate,
@.country, @.desc, @.pic
END
PRINT 'SET IDENTITY_INSERT MarinLif OFF'
CLOSE CURS
DEALLOCATE CURS

I am trying to have a script with data that i have in a table for the
purpose of inserting this data in another database under the same table
name. The output of my script is just print statements. I will save
these statements and later on i will execute them on the other database
table.
The problem is with the image and text. Can u give me an example on how
to retrieve data from the image for my script?

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!MC (anonymous@.discussions.microsoft.com) writes:
> I am writing a script to retrieve data records of MarinLif table. The
> problem is that i have a column MarinLif_Picture of type image so the
> insert is wrong in this way. How do i correct it?
>...
> I am trying to have a script with data that i have in a table for the
> purpose of inserting this data in another database under the same table
> name. The output of my script is just print statements. I will save
> these statements and later on i will execute them on the other database
> table.
> The problem is with the image and text. Can u give me an example on how
> to retrieve data from the image for my script?

You can't assign to image variables, so this approach is not going
to work.

You are probably better off using BCP, a command-line which is designed
for importing and exporting data. In this case you could try:

bcp yourdb..MarinLif out MarinLif.bcp -N -T -S source_server
bcp yourotherdb..MarinLif in MarinLif.bcp -N -T -S target_server

-N here means that you are using native datatypes with Unicode. -T is for
trusted connection. -S specifies the server.

For -N to work, the tables must be identical, including column order.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To add to Erland's response, if both databases are on the same server, you
can use the INSERT INTO... SELECT syntax:

INSERT INTO MyOtherDatabase.dbo.MarinLif
(
MarinLIf_ID,
MarinLIfTyp_ID,
MarinLif_name,
MarinLIf_ScName,
MarinLIf_Distribution,
MarinLIf_MaxSize,
MarinLIf_Env,
MarinLIf_climate,
MarinLIf_Country,
MarinLIf_Desc,
MarinLIf_Pic
)
SELECT
MarinLIf_ID,
MarinLIfTyp_ID,
MarinLif_name,
MarinLIf_ScName,
MarinLIf_Distribution,
MarinLIf_MaxSize,
MarinLIf_Env,
MarinLIf_climate,
MarinLIf_Country,
MarinLIf_Desc,
MarinLIf_Pic
FROM MyDatabase.dbo.MarinLif

--
Hope this helps.

Dan Guzman
SQL Server MVP

"MC" <anonymous@.discussions.microsoft.com> wrote in message
news:40c4c158$0$165$c397aba@.news.newsgroups.ws...
>
> I am writing a script to retrieve data records of MarinLif table. The
> problem is that i have a column MarinLif_Picture of type image so the
> insert is wrong in this way. How do i correct it?
>
> /*****MarinLIf TAble ********************/
> DECLARE @.ID INT
> DECLARE @.typeID INT
> DECLARE @.NAME NVARCHAR(50)
> Declare @.scName nvarchar(50)
> Declare @.distribution nvarchar(600)
> Declare @.maxSize nvarchar(200)
> Declare @.env nvarchar(200)
> Declare @.climate nvarchar(200)
> Declare @.country nvarchar(2000)
> Declare @.desc nvarchar(4000)
> Declare @.pic image
> DECLARE CURS CURSOR STATIC FOR
> SELECT MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
> MarinLIf_ScName,MarinLIf_Distribution,
> MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
> _Desc,MarinLIf_Pic
> FROM MarinLif
> OPEN CURS
> FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
> @.maxSize, @.env, @.climate,
> @.country, @.desc, @.pic
> PRINT 'SET IDENTITY_INSERT MarinLif ON'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'INSERT INTO MarinLif (MarinLIf_ID, MarinLIfTyp_ID,MarinLif_name,
> MarinLIf_ScName,MarinLIf_Distribution,
> MarinLIf_MaxSize,MarinLIf_Env,MarinLIf_climate,Mar inLIf_Country,MarinLIf
> _Desc,MarinLIf_Pic)
> VALUES (' + convert(varchar,@.ID) + ','
> + convert(varchar,@.typeID) + ','
> + '''' + @.NAME + '''' +
> + '''' + @.scName + '''' + ','
> + '''' + @.distribution + '''' + ','
> + '''' + @.maxSize + '''' + ','
> + '''' + @.env + '''' + ','
> + '''' + @.climate + '''' + ','
> + '''' + @.country + '''' + ','
> + '''' + @.desc + '''' + ','
> + '''' + @.climate + '''' + ','
> + '''' + @.pic + '''' + ')'
> FETCH NEXT FROM CURS INTO @.ID,@.typeID,@.NAME, @.scName, @.distribution,
> @.maxSize, @.env, @.climate,
> @.country, @.desc, @.pic
> END
> PRINT 'SET IDENTITY_INSERT MarinLif OFF'
> CLOSE CURS
> DEALLOCATE CURS
> I am trying to have a script with data that i have in a table for the
> purpose of inserting this data in another database under the same table
> name. The output of my script is just print statements. I will save
> these statements and later on i will execute them on the other database
> table.
> The problem is with the image and text. Can u give me an example on how
> to retrieve data from the image for my script?
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Data Retrieval From Online Database - Paid Consultant Required

Hi,

I wonder if anyone can help with the following on a fee paying basis
for the the design or development of some type of script or utility or
piece of code to do the following work.

I wish to retrieve some data from an online database that is in the
public domain. The online database has a search facility that matches
the entry (name) in a search box then returns a screen stating that a
match has been found or not found.

If a match is found there is a button to click that proceeds to the
screen containing the data which is simply two names. It is these two
names that I wish to retrieve and store them in something like a text
file where they are associated with the original entry (name) used in
the search box.

I have a list of the entries for the search box that can be supplied
in sample format for testing as a columnar text file or comma
delimited file or a spreadsheet.. I would need the procedure to
process the list of search entries, retrieve the data then move on to
the next one in the list. Obviously, if a match was not found then the
process would need to move on the the next entry in the list with
perhaps a message saying "No Match" until the entire list was
processed.

The PC I hope to run the process on is a Windows XP machine and if I
need to purchase any particular software that is necessary for the job
then I am quite willing to do so. Also, as I pointed out above I would
pay for the work to be done.

I hope that I have explained the above OK and that I have posted it to
the correct newsgroup(s). If it is not in the correct newsgroup I
would be grateful if anyone could point me in the right direction.

Regards
Dave GibsonReplied in microsoft.public.sqlserver.programming.

--
David Portas
SQL Server MVP
--sql

Data Processing Script Justs Stops

I have a problem with a stored procdure I wrote. This proc will
process 80 to 100 million every night, it aggregates, inserts into a
different table, and then deletes the original data. The process will
hang after processing 20 to 40 million records. I must stop the proc
and then restart it before I can complete the process.

Any ideas why it would hang?

Current Specs:
Hardware: Compaq Proliant 370 - Dual 933MHz - 1GB or Ram - 35 gig Raid
5
OS: Windows 2k SP4
DB: MS SQL 2000 Enterprise SP3Not without seeing the SP.
Maybe the transaction log is unable to grow?

Nigel Rivett
www.nigelrivett.net

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Data Migration - Insert Script / Fresh install

Hi

I have some tables in my SQL Server 2005 database. I need to create insert scripts for them. Since these tables contain the CLOB and BLOB values and the number of records is huge, I need to devise a way to install this table data on any new database(Assuming the new database is created and the table structure exists) .Can some one suggest me how I can do this ?

DTS or SSIS is out of question because least manual interference is required.

Thanks & Regards
ImtiazYou could use BCP to export the data into text/binary files and then import it back. This is the most efficient way. Generating insert statements is not cumbersome (there are some 3rd party tools that does this). But the files will still be huge and difficult to manage.

data import question

Hi,
I am writing a script for a client to update TableA using the data in
TableB. I know how to write an UPDATE query to do this. But the client wants
an ability to add or remove fields that they update using this script. I
know I can do this by letting the client enter the names of the source and
destination fields in a custom table and generate a dynamic query to update
the provided columns. But the problem is there are large number of fields
and sql has the limit of 8000 characters on variables write a dynamic query.
So writing a dynamic query is not an easy option either. So my question is
how do I solve this problem. Please not that we need to update 150+ fields
in 500,000+ records using this script. So it also needs to be as fast as
possible.
Thanks in advance.helpful sql wrote:

> Hi,
> I am writing a script for a client to update TableA using the data in
> TableB. I know how to write an UPDATE query to do this. But the client wan
ts
> an ability to add or remove fields that they update using this script. I
> know I can do this by letting the client enter the names of the source and
> destination fields in a custom table and generate a dynamic query to updat
e
> the provided columns. But the problem is there are large number of fields
> and sql has the limit of 8000 characters on variables write a dynamic quer
y.
> So writing a dynamic query is not an easy option either. So my question is
> how do I solve this problem. Please not that we need to update 150+ fields
> in 500,000+ records using this script. So it also needs to be as fast as
> possible.
> Thanks in advance.
Here's one possibility:
UPDATE A
SET
col1 = CASE WHEN @.col1 = 'Y' THEN B.col1 ELSE A.col1 END,
col2 = CASE WHEN @.col2 = 'Y' THEN B.col2 ELSE A.col2 END,
col3 = CASE WHEN @.col3 = 'Y' THEN B.col3 ELSE A.col3 END
..
WHERE ...?
David Portas
SQL Server MVP
--|||Dynamic SQL can take several variables to exceed the 8000 byte limit.
DECLARE @.X1 VARCHAR(8000), @.X2 VARCHAR(8000)
EXEC(@.X1 + @.X2)
Andrew J. Kelly SQL MVP
"helpful sql" <nospam@.stopspam.com> wrote in message
news:eC2Vg$IDGHA.3748@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am writing a script for a client to update TableA using the data in
> TableB. I know how to write an UPDATE query to do this. But the client
> wants an ability to add or remove fields that they update using this
> script. I know I can do this by letting the client enter the names of the
> source and destination fields in a custom table and generate a dynamic
> query to update the provided columns. But the problem is there are large
> number of fields and sql has the limit of 8000 characters on variables
> write a dynamic query. So writing a dynamic query is not an easy option
> either. So my question is how do I solve this problem. Please not that we
> need to update 150+ fields in 500,000+ records using this script. So it
> also needs to be as fast as possible.
> Thanks in advance.
>