-
Feb 12th, 2020, 12:28 AM
#1
Thread Starter
Fanatic Member
Sql Server copying table
I use this query to copy an existing table structure without data:
Code:
SELECT TOP 0 * INTO BusDataNew FROM BusData
It works well, but in the process no contraints are copied and I need to add queries (in SSMS) to add default values to fields and PK's and FK's to the table.
The internet queries I have found for this are difficult to interpret and use.
Any help will be apreciated.
Thanks
PK
-
Feb 12th, 2020, 01:03 AM
#2
Re: Sql Server copying table
since you are using sql server, why don't you script your existing table as "create" and then just rename the table in the script? Your new table would then have all of the constraints your current one has.
-
Feb 12th, 2020, 01:07 AM
#3
Thread Starter
Fanatic Member
Re: Sql Server copying table
Ok thanks, I will rather do that then.
PK
-
Feb 12th, 2020, 03:12 AM
#4
Re: Sql Server copying table
Originally Posted by Lord Orwell
since you are using sql server, why don't you script your existing table as "create" and then just rename the table in the script? Your new table would then have all of the constraints your current one has.
Or write a stored procedure with an IN-Parameter being the new tablename
https://social.msdn.microsoft.com/Fo...um=transactsql
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Feb 12th, 2020, 08:46 AM
#5
Re: Sql Server copying table
You can start with this and convert it to a create table using the results
Code:
SELECT
COLUMN_NAME AS [Field Name] ,
DATA_TYPE AS [Data Type] ,
Case DATA_TYPE
WHEN 'char' THEN CASE CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(1000), CHARACTER_MAXIMUM_LENGTH) END
WHEN 'nchar' THEN CASE CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(1000), CHARACTER_MAXIMUM_LENGTH) END
WHEN 'varchar' THEN CASE CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(1000), CHARACTER_MAXIMUM_LENGTH) END
WHEN 'nvarchar' THEN CASE CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(1000), CHARACTER_MAXIMUM_LENGTH) END
WHEN 'decimal' THEN '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) + ',' + CONVERT(VARCHAR(10), NUMERIC_SCALE) + ')'
WHEN 'float' THEN '(' + CONVERT(VARCHAR(10), NUMERIC_PRECISION) + ',' + CONVERT(VARCHAR(10), NUMERIC_SCALE) + ')'
WHEN 'datetimeoffset' THEN '(' + CONVERT(VARCHAR(10), DATETIME_PRECISION) + ')'
END AS [Sizing (Percision/Scale)] ,
CASE INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE
WHEN 'YES' THEN 'Yes'
ELSE 'No'
END AS [Nullable] ,
CASE COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [Identity] ,
COLUMN_DEFAULT AS [Default Value] ,
CASE COLUMNPROPERTY(object_id(TABLE_NAME),column_name,'IsComputed' )
WHEN 1 THEN 'Yes'
ELSE NULL
END AS [Computed Column] ,
ORDINAL_POSITION AS [Position in Table]
,CC.definition AS [Caclulated Field Formula]
,MC.masking_function AS [Masking Function]
FROM INFORMATION_SCHEMA.COLUMNS
LEFT OUTER JOIN sys.computed_columns CC
ON INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = CC.name
LEFT OUTER JOIN sys.masked_columns MC
ON INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = MC.name
AND ORDINAL_POSITION = MC.column_id
AND MC.object_id = (SELECT object_id FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id WHERE sys.objects.name = 'thetableName' AND sys.schemas.name = 'theShecema')
WHERE TABLE_SCHEMA = 'theShecema'
AND TABLE_NAME = 'thetableName'
ORDER BY ORDINAL_POSITION
I also have other parts that will create the code for PK, and indexes, FKs, check contstraints, and triggers
Last edited by GaryMazzone; Feb 12th, 2020 at 09:26 AM.
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Feb 12th, 2020, 09:11 AM
#6
Re: Sql Server copying table
script your existing table as "create" and then just rename the table in the script?
That's what I always do but don't forget to rename all the constraints too
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Feb 12th, 2020, 09:21 AM
#7
Re: Sql Server copying table
Originally Posted by FunkyDexter
That's what I always do but don't forget to rename all the constraints too
That's why i said to write a parametrised SP with an IN-Parameter
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Feb 12th, 2020, 09:49 AM
#8
Thread Starter
Fanatic Member
Re: Sql Server copying table
I thank you all for such rich contributions which I will disseminate.
PK
-
Feb 13th, 2020, 08:04 AM
#9
Re: Sql Server copying table
Originally Posted by Zvoni
That's why i said to write a parametrised SP with an IN-Parameter
That's really only necessary if the table copy isn't a one-off...
But then when I see people writing code to "copy" tables but not the data... I start getting suspicious about their design and wondering what exactly they are up to, and that their design isn't proper.
Typically I see this a lot in"accounting"systems where the dev seems to think that they need a new table for each year.
-tg
-
Feb 13th, 2020, 08:22 AM
#10
Re: Sql Server copying table
tg, true.
What actually baffles me (and i repeat myself: no practical experience in MSSQL): I know from SQLite and MySQL, that i can get a "CREATE"-Statement for any table (at least in DBBrowser for SQLite and MySQL-Workbench).
I would be surprised, if SSMS doesn't provide something like that.
Get that CREATE-Statement, c&p to TextEditor (e.g. Notepad), change the "old" tablename to the new one (Search&Replace), c&p it back into SSMS, FIRE!
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Feb 13th, 2020, 09:15 AM
#11
Re: Sql Server copying table
Originally Posted by Zvoni
tg, true.
What actually baffles me (and i repeat myself: no practical experience in MSSQL): I know from SQLite and MySQL, that i can get a "CREATE"-Statement for any table (at least in DBBrowser for SQLite and MySQL-Workbench).
I would be surprised, if SSMS doesn't provide something like that.
Get that CREATE-Statement, c&p to TextEditor (e.g. Notepad), change the "old" tablename to the new one (Search&Replace), c&p it back into SSMS, FIRE!
It does... right click on any table and select "Script As..." and there's options for complete CRUD statements, as well as CREATE... and the CREATE option has options, with/without constraints|Indexes|etc.
However, I suspect in this case, he's creating new tables based on old ones in the app, and it's not necessarily a one-off case, which leads me to suspect a flawed design.
-tg
-
Mar 6th, 2020, 12:17 AM
#12
Lively Member
Re: Sql Server copying table
Hello,@Peekay
Please try this query,To Sql Server copying table:
Code:
SELECT * INTO ABC_1 FROM ABC;
I hope above query will be useful for you.
Thank you.
-
Mar 6th, 2020, 03:33 AM
#13
Thread Starter
Fanatic Member
Re: Sql Server copying table
Prahlad. Your query does not copy the table attributes.
Lord Orwell suggested the best solution.
PK
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|