Good question, I learned something new. Thanks!

Gkerscher, I can use the pivot operator too but the problem is that my col2 values are not limited to [A],,[C],[D],[F],[G],[T]. I already have around 500 different values in there...

Thanks Lutz!

Hi Grant,

I tried using a subquery as well as CTE, the results for ColA3 are not right.

Suppose Table B has 4 rows that need to be inserted into Table A....

In SSMS, if you write the statement in 1 line, you get a syntax error:

print 'Hey what''s going on ?';GO 10

But changing it to 2 lines works fine:

print 'Hey...

The execution plan for my original query and the derived table query is exactly the same with 50%-50% cost. I am sure that if I add the complexity for multiple...

Thanks for the suggestions and the comment about possible multiple values of Col2 for Tab2. I will try different structures and check which execution plan looks better.

Thanks for the information Jason!

For now I was doing this on my Development server which is a 32 bit 2005 connecting to 32 bit 2000. But when I move...

I am connecting to another SQL Server 2000 instance.

I was using 'Other data source' and 'SQL Native Client' to configure linked server. After looking at Jason's example I changed it...

I did add login U1 in security tab under the 'Local Server login to remote server login mappings' section and provided the remote user and remote password there. That did...

Andy Warren (7/22/2008)

---

Have to find the tipping point. Drop/create may or may not be faster, for example if you are adding 1 million rows to a 100 million row table....

I am looking for a place to specify Block Size for the linked server.

The Network Packet Size for our SQL Server is 8192, I am not sure if linked...

Thanks for the headsup MentalWhiteNoise! We actually reboot our SQL Server everyday and so I would have run into this issue pretty soon.

I am doing some testing using DB2OLEDB, IBMDASQL...

Thanks a lot for the link Jeffrey!!

only option 2 is the correct answer, option 3 does not give the results in the required format!