.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Saving primary key constraint when creating new table using SELECT INTO statement - SQL Server 2008

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :
 

Using SQL Server 2008:

We have a main database - MAIN.   For monthly data extracts, I create new tables with relevant data in another database called EXTRACT.

I use SELECT INTO statements to create the tables in the EXTRACT schema.  How do I preserve the primary key constraints in the EXTRACT tables?  Do I need to write separate queries to set them?

 




View Complete Post


More Related Resource Links

Saving primary key constraint when creating new table using SELECT INTO statement - SQL Server 2008

  

Using SQL Server 2008:

We have a main database - MAIN.   For monthly data extracts, I create new tables with relevant data in another database called EXTRACT.

I use SELECT INTO statements to create the tables in the EXTRACT database.  How do I preserve the primary key constraints in the EXTRACT tables?  Do I need to write separate queries to set them?

 


SQL Server 2008 - Alter Table Switch Statement Fails Where Partition Key Defined as a Persisted Comp

  

Here's a very annoying and poorly (or undocumented) issue in SQL Server 2008 Ent. Ed. post-SP1, CU8:

When creating a table with a persisted computed column as the clustered primary and partitioning key (see below), where the computation includes a call to CONVERT, watch out for the representation of this calculation on sys.computed_columns.  The query parser will modify the definition at table creation to insert a default style parameter of "(0)", if no other style is specified.  If the original definition did not include any style parameter and you later attempt to programmatically recreate the table using the stored definition contained on sys.computed_columns, the query optimizer will throw an error when you then attempt to switch partitions from the original table into the new programmatically created table, as follows:

Msg 4966, Level 16, State 1, Line 72

ALTER TABLE SWITCH statement failed. Computed column '%' defined as '%' in table %' is different from the same column in table %' defined as '%'.

This is so, eventhough the two definitions are completely functionally equivalent, considering the default value of the style parameter.  This can cause a lot of headaches, because correcting the problem requires dropping and rebuilding the computed column with a definition that include

Under the Table: Programming with FileStreams in SQL Server 2008

  

There's always been disagreement about whether large blobs, such as document and multimedia items, should be stored in the database or file system. In SQL Server 2008 you don't have to choose; filestream storage provides the best of both approaches.

Bob Beauchemin

MSDN Magazine May 2009


Under The Table: Spatial Data Support In SQL Server 2008

  

New spatial data support in SQL Server 2008 opens the door to mapping and querying geometric and geographic data, allowing you to build exciting new applications.

Bob Beauchemin

MSDN Magazine February 2009


SQL DIAGOSITC MANAGER tools capture "SELECT *" statement while ALTER / ADD CHECK CONSTRAINT is execu

  
Hello  ,   I exectuted the code(alter/add with check)  below in SSMS . My_table has 200 millions records . While running the alter/add with check query  the server crashed and the “SQL DIAGOSITC MANAGER” which run on the top of sql serever alert the DBA that select * from my_table was obtained from the last command issued by the user using a DBCC command.      Where does the select * from my_table comes from ? I didn’t excuted select * from my_table at all .   After the sql serever re started I was able to run the command below with out any problem. I checked the SQL PROFILER for the trace there is no “select * from my_table” .   So again where this select * from my_table comes from ?   Here is my initial query ----------------------------------------------------------------- ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key1] FOREIGN KEY([Key1]) REFERENCES [Dbo].[Dim_table1] ([Key1]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key1] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key2] FOREIGN KEY([key2]) REFERENCES [Dbo].[Dim_table2] ([key2]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key2] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key3] FOREIGN KEY([d], [c], [r], [g]) REFERENCES [Dbo].my

SELECT statement to return NULL by matching data from another table.

  
Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

Creating a table-valued function on a user defined type in SQL Server CLR

  
We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

sql server 2008 Table Partition

  
HI i want to implement partitioning in my sql server 2008 database table http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx   i have used this link. but when i try this link then i am able to create only primary file group no secondary file group..so help me also by fault i have created partition in wrong table .so i want to detete tha partition from that table but of i delete the PARTITION SCHEME and PARTITION FUNCTION THEN it throws error..so what shoul i do to remove partition from my current table

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

sql server 2008 Table Partition

  
HI http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx  i have used  above link to create partition,but my problem is i want to do partition on daily basic, one column in my table is date(by default has getdate()),on the base of this i want to do partition, eg.in this link i need to give time duration between which one can do partition, and also either daily or monthly or quarterly or yearly need to be specify ..if i give date between 01/08/2010 to 01/08/2012 and then select daily wise partition which i am in need then table create more than 700 partion but problem comes when i need to assign group to them how cud i select Primary file group more than 700 times to assign it to each partiotion.it becomes very lengthy process  Please help me

Partitioning a table in SQL Server 2008

  
Our application has got few tables which has grown to a larger size. Hence one suggestion put forward is to consider table partitioning. Since this does not involve any code changes in the application, we are looking into this option. 1. Is there any disadvantage if we use table partitioning feature 2. Will table partitioning affect the current indexes that exist on the table? 3. Database mirroring is enabled in our schema. Will partitioning have any effect on this? 4. Any suggestion on how we should try this out?  

SQL SERVER 2000 Using IIF in SELECT Statement

  
Hi Good Guys, I need your help, Please help me. I am trying to retrieve data from SQL SERVER 2000 Table, TBLSupplier whose ActiveStatus datatype is BIT which consist of value 0 (zero) or 1 (one).  to display on the VBNT2008 DataGridView. But the SQL String Select statment is not working. Error message: Incorrect syntax near '=' Select SupplierId, SupplierName, IIF (ActiveStatus =1, 'YES', 'NO') as [Status] from TblSupplier

Sql Server Select statement

  
selet * from customer where status&1 = 1 what is meaning for & update customer set status = staus | 1 what is meaning for |

What is fastest way of reading large size XML in SQL Server 2005 using Select statement ?

  
What is fastest way of reading large size XML in SQL Server 2005 using Select statement ?

SQL Server 2008: When creating database with filestream group pc hangs

  
Hello,

I am facing this STRANGE problem with sql server 2008. When I try to create a new database with filestream group my system hangs. When I restart pc by direct power off and then on, I cannot see the new database but at the given location I could see that database files and filestream group has been created. 

Solutions Tried.
Uninstall, download new version then reinstall but to no use.
Set maximum permissions to current user (according to my knowledge).
Using Mix mode authentication.


PC configuraion:
XP Home Edition SP2,
Intel Dual Core 2.0 GHZ Processor
4GB Ram,
SQL Server express SP1 installed on NTFS Partition
Plenty of Disk Space
Comodo Firewall pro, Avast Antivirus ( Already tried to stop firewall and antivirus still no use)

Any help would be appreciated.

Find '' in a table in MS SQL SERVER 2008

  

Hi,

I need to find white spaces in my table. the table contains 35 columns, therefore, I cannot check with AND or OR operator something like this

SELECT * FROM MS_Exchange_IS WHERE (TMZDIFF != '' AND Timestamp != ''..........)

Also I don't want to use cursors as it will greatly hamper the performance of my application.

Please suggest any other option.

 

Any link or pointers would be helpful

 

Thanks in Advance,


ashu
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend